Как массовая вставка с динамическим значением для столбца
ситуация такая:
У меня есть 200 текстовых файлов с разными именами, как 601776.txt имя каждого файла на самом деле является ID_foo, и он содержит некоторые данные, подобные этим (2 столбца):
04004 Albánchez
04006 Albox
04008 Alcóntar
04009 Alcudia de Monteagud
.
.
.
теперь я хочу БОЛЬШАЯ ВСТАВКА эти файлы TXT в таблицу SQL Server, которая имеет 3 столбца, один из этих столбцов должен быть именем файла TXT. Я использую сценарий PHP, поэтому я сделал цикл, чтобы получить имена файлов и что дальше?
BULK INSERT Employee_Table
FROM '../home/601776.txt'
WITH (
FIELDTERMINATOR ='\t',
ROWTERMINATOR = ''\n''
)
Как я могу установить третий столбец при массовой вставке с переменной $ file_name в каждом цикле?
Считаете ли вы, что это лучшая идея, если можно вставить таблицу, читая текстовый файл построчно? И как?
Спасибо
Решение
Это один из немногих случаев, когда курсор на самом деле идеален в 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
Другие решения
Других решений пока нет …
detector