Skip to content

Instantly share code, notes, and snippets.

@csprofile
Created February 16, 2017 20:03
Show Gist options
  • Save csprofile/4408c09a172174f72846b159fda976b1 to your computer and use it in GitHub Desktop.
Save csprofile/4408c09a172174f72846b159fda976b1 to your computer and use it in GitHub Desktop.
CREATE DATABASE AccessX
GO
USE AccessX
GO
CREATE TABLE [dbo].[AccessDB](
[LOJA] [nvarchar](50) NULL,
[DATA] [nvarchar](50) NULL,
[CODIGO DO MATERIAL] [nvarchar](50) NULL,
[QUANTIDADE] [nvarchar](50) NULL,
[TOTAL DO ITEM] [nvarchar](50) NULL,
[intFinalEntrada] [int] NULL,
[intFinalAno] [int] NULL,
[strFinalNF] [nvarchar](255) NULL,
[strFinalCNPJ] [nvarchar](255) NULL,
[strFinalMaterial] [nvarchar](255) NULL,
[dtmFinal] [datetime] NULL,
[strFinalTipo] [nvarchar](255) NULL,
[strFinalBase] [nvarchar](255) NULL,
[dblFinalQuantidade] [float] NULL,
[dblFinalAliquota] [float] NULL,
[strCruzamento] [nvarchar](255) NULL,
[dblFinalBaseST] [float] NULL,
[dblFinalBaseUnitaria] [float] NULL,
[dblFinalBaseProporcional] [float] NULL,
[dblFinalDiferenca] [float] NULL,
[dblFinalCompletar] [float] NULL,
[dblFinalRessarcir] [float] NULL
) ON [PRIMARY]
GO
DECLARE @tableName VARCHAR(100)
DECLARE @FileName VARCHAR(100)
DECLARE @Id int = 0
DECLARE @Query NVARCHAR(MAX)
DECLARE @MaxId int
DECLARE @files TABLE (ID int IDENTITY, FileName varchar(100))
INSERT INTO @files EXECUTE xp_cmdshell 'dir c:\x /b'
SELECT @MaxId = MAX(Id) FROM @files
WHILE (@Id < @MaxId) BEGIN
SELECT
@FileName = [FileName]
,@tableName = REVERSE(SUBSTRING(REVERSE([FileName]), CHARINDEX('.', REVERSE([FileName])) + 1, 999))
FROM
@files
WHERE ID = @Id
SET @Query =
'INSERT INTO dbo.[AccessDB] SELECT * FROM OPENDATASOURCE(
''Microsoft.ACE.OLEDB.15.0'',
''Data Source="c:/x/'+@FileName+'"'')...['+@tableName+']'
EXECUTE sp_executesql @Query
SET @Id = @Id + 1
END
SELECT * FROM dbo.[AccessDB]
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment