动态批量插入来自不同位置文件夹的多个 csv 文件

Question

我在不同的位置文件夹中有多个csv文件。我想在SQL服务器中动态进行批量插入，这将在单个表中进行批量插入。 我为单个CSV文件做了这件事。有人可以帮助我吗？

Answer 1

这里有一些东西可以帮助你开始。您可以阅读xp_dirtree和cursors以了解它们的工作原理。如果您的文件分布在不同的父文件夹或不同的驱动器上，您将需要一个额外的光标来获取它们......

---------------------------------------------------------------------------------------------------------------
--Set some variables
---------------------------------------------------------------------------------------------------------------
DECLARE @fileLocation VARCHAR(128) = '\servere$data'                        --location of files (parent folder)
DECLARE @sql NVARCHAR(4000)                                                     --dynamic sql variable
DECLARE @fileName VARCHAR(128)                                                  --full file name variable if you want to use this

---------------------------------------------------------------------------------------------------------------
--Get a list of all the file names in the directory
---------------------------------------------------------------------------------------------------------------
IF OBJECT_ID('tempdb..#FileNames') IS NOT NULL DROP TABLE #FileNames
CREATE TABLE #FileNames (
id int IDENTITY(1,1)
,subdirectory nvarchar(512)
,depth int
,isfile bit)
INSERT #FileNames (subdirectory,depth,isfile)
EXEC xp_dirtree @fileLocation, 1, 1

--Here's all the files and folders. Note isFile field.
select * from #FileNames

---------------------------------------------------------------------------------------------------------------
--Create a cursor to fetch the file names
---------------------------------------------------------------------------------------------------------------
DECLARE c CURSOR FOR
select name from #FileNames where isfile = 1
OPEN c
FETCH NEXT FROM c INTO @fileName
---------------------------------------------------------------------------------------------------------------
--For each file, bulk insert to the proper view, update the proper table, update the log, etc...
---------------------------------------------------------------------------------------------------------------

WHILE @@FETCH_STATUS = 0
BEGIN
--do your bulk insert work
FETCH NEXT FROM c INTO @fileName
END