如何使用列的动态值进行批量插入

Question

情况是这样的：

我有 200 个不同名称的 txt 文件，例如 601776.txt每个文件的名称实际上都是一个ID_foo，它包含一些这样的数据(2 列(：

04004 Albánchez
04006 Albox
04008 Alcóntar
04009 Alcudia de Monteagud
.
.
.

现在我想将这些 TXT 文件批量插入到具有 3 列的 SQL Server 表中，其中一列应该是 txt 文件的名称。我使用的是PHP脚本，所以我做了一个循环来获取文件名，然后呢？

BULK INSERT Employee_Table
    FROM '../home/601776.txt'  
    WITH (  
         FIELDTERMINATOR ='t',  
         ROWTERMINATOR = ''n'' 
        )  

如何在每个循环中使用 $file_name 变量批量插入时设置第三列？

您认为如果可以通过逐行读取 txt 文件来插入表格会更好吗？又如何？

谢谢

Answer 1

这是为数不多的游标在 SQL Server 中实际上是理想的情况之一。这里有一种方法。一旦您看到 PRINT 语句并感到满意，您就可以将其注释掉并取消注释掉它下面的两行。我添加了一些逻辑来添加通常需要的文件名和处理日期，但您的表定义将需要这些列。它应该把这个想法传达出去。

---------------------------------------------------------------------------------------------------------------
--Set some variables
---------------------------------------------------------------------------------------------------------------
DECLARE @dt VARCHAR(10)                                                         --date variable but stored as VARCHAR for formatting of file name
DECLARE @fileLocation VARCHAR(128) = 'E:DATA_TRANSFERS'                       --production location which is \issqlstd01 but the xp_dirtree didn't like this
DECLARE @sql NVARCHAR(4000)                                                     --dynamic sql variable
DECLARE @fileName VARCHAR(128)                                                  --full file name variable

---------------------------------------------------------------------------------------------------------------
--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


---------------------------------------------------------------------------------------------------------------
--Create a cursor to fetch the file names
---------------------------------------------------------------------------------------------------------------
DECLARE c CURSOR FOR
select subdirectory from #FileNames where isfile = 1
OPEN c
FETCH NEXT FROM c INTO @fileName
---------------------------------------------------------------------------------------------------------------
--For each file, bulk insert 
---------------------------------------------------------------------------------------------------------------

WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @sql = 'BULK INSERT Employee_Table FROM '''+ @fileLocation + @fileName +''' WITH (FIELDTERMINATOR = ''t'',KEEPNULLS,ROWTERMINATOR = ''0x0a'')'

        --Try the bulk insert, if error is thrown log the error 
        --Also update the Table Columns which aren't a part of the original file (load date and original file name)
        BEGIN TRY
            PRINT(@sql)
            --EXEC(@sql)
            --UPDATE Employee_Table SET OrigFile = @fileName, LoadDate = GETDATE() WHERE OrigFile IS NULL
        END TRY
        BEGIN CATCH
            SELECT ERROR_MESSAGE()
        END CATCH
        FETCH NEXT FROM c INTO @fileName
    END
CLOSE c
DEALLOCATE c
GO