从文件夹将多个CSV文件导入SQL Server

Question

我有一个名为“转储”的文件夹。 此文件夹包含各种.CSV文件。文件夹位置为“ C：\ Dump”

我想将这些文件的内容导入SQL Server。我想要粗糙的代码以及适当的注释，以便我理解。

我已经尝试了一些在网上找到的代码。但是由于某些奇怪的原因，他们还没有为我解决问题。

我想要的步骤是

步骤1：将文件夹中的所有文件名复制到表中

步骤2：遍历表并使用批量插入从文件中复制数据。

请有人帮我解决这个问题。在此先感谢:)

Answer 1

--BULK INSERT MULTIPLE FILES From a Folder&nbsp;&nbsp; &nbsp; --a table to loop thru filenames drop table ALLFILENAMES&nbsp; &nbsp; CREATE TABLE ALLFILENAMES(WHICHPATH VARCHAR(255),WHICHFILE varchar(255))&nbsp; &nbsp; --some variables&nbsp; &nbsp; declare @filename varchar(255),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; @path&nbsp; &nbsp; &nbsp;varchar(255),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; @sql&nbsp; &nbsp; &nbsp; varchar(8000),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; @cmd&nbsp; &nbsp; &nbsp; varchar(1000)&nbsp; &nbsp; --get the list of files to process:&nbsp; &nbsp; SET @path = 'C:\Dump\'&nbsp; &nbsp; SET @cmd = 'dir ' + @path + '*.csv /b'&nbsp; &nbsp; INSERT INTO&nbsp; ALLFILENAMES(WHICHFILE)&nbsp; &nbsp; EXEC Master..xp_cmdShell @cmd&nbsp; &nbsp; UPDATE ALLFILENAMES SET WHICHPATH = @path where WHICHPATH is null&nbsp; &nbsp; --cursor loop&nbsp; &nbsp; declare c1 cursor for SELECT WHICHPATH,WHICHFILE FROM ALLFILENAMES where WHICHFILE like '%.csv%'&nbsp; &nbsp; open c1&nbsp; &nbsp; fetch next from c1 into @path,@filename&nbsp; &nbsp; While @@fetch_status <> -1&nbsp; &nbsp; &nbsp; begin&nbsp; &nbsp; &nbsp; --bulk insert won't take a variable name, so make a sql and execute it instead:&nbsp; &nbsp; &nbsp; &nbsp;set @sql = 'BULK INSERT Temp FROM ''' + @path + @filename + ''' '&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;+ '&nbsp; &nbsp; &nbsp;WITH (&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;FIELDTERMINATOR = '','',&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ROWTERMINATOR = ''\n'',&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;FIRSTROW = 2&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ) '&nbsp; &nbsp; print @sql&nbsp; &nbsp; exec (@sql)&nbsp; &nbsp; &nbsp; fetch next from c1 into @path,@filename&nbsp; &nbsp; &nbsp; end&nbsp; &nbsp; close c1&nbsp; &nbsp; deallocate c1&nbsp; &nbsp; --Extras&nbsp; &nbsp; --delete from ALLFILENAMES where WHICHFILE is NULL&nbsp; &nbsp; --select * from ALLFILENAMES&nbsp; &nbsp; --drop table ALLFILENAMES

Answer 2

要扩展SarangArd的答案，如果文件名与表名匹配，则可以将temp替换为以下内容。' + Left(@filename, Len(@filename)-4) + '