sql-server - 从不同位置文件夹动态批量插入多个 csv 文件

Question

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

Answer 1

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

---------------------------------------------------------------------------------------------------------------
--Set some variables
---------------------------------------------------------------------------------------------------------------

DECLARE @fileLocation VARCHAR(128) = '\\server\e$\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