如何批量插入具有动态值的列

本文介绍了如何批量插入具有动态值的列的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

情况是这样的：

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

I have 200 txt files with different names like 601776.txt each file's name is actually an ID_foo and it contains some data like this (2 columns):

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

现在我要批量插入这些TXT文件到SQL Server表中，具有3列，这些列之一应为txt文件的名称。我使用的是PHP脚本，因此循环搜索了文件名，然后呢？

now I wanna BULK INSERT these TXT files into a SQL Server Table which has 3 column one of these columns should be the name of the txt file. I'm using a PHP script, so I made a loop to get the file names and then what?

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

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

how can i set the third column while bulk inserting with $file_name variable in each loop?

您认为这是一个更好的主意吗？可以通过逐行读取txt文件来插入表格？以及如何？

Do you think it is a better idea if it is possible to insert the table by reading the txt file line by line? And how?

谢谢

推荐答案

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

This is one of the few times that a cursor is actually ideal in SQL Server. Here's a way. Once you see the PRINT statement and are satisfied you can comment it out and uncomment out the two lines below it. I put some logic in to add the file name and a processed date which is usually needed, but your table definition would need these columns. It should get the idea across.

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