August 25, 2016 at 6:53 am
Hello All,
Need your advise, please: I have to scan a folder for a given type of files, e.g. ".sql" and load their content into a temporary table.
Currently I use xp_dirtree as below to obtain the list of files and record them into #directoryTree.
DECLARE @sql_files_path VARCHAR(100) = 'C:\temp\SQLScripts';
IF OBJECT_ID('tempdb..#directoryTree') IS NOT NULL
DROP TABLE #directoryTree;
CREATE TABLE #directoryTree (
idINT IDENTITY(1,1),
SQL_file_nameNVARCHAR(500),
depthINT,
is_fileBIT);
INSERT INTO #directoryTree
EXEC xp_dirtree @sql_files_path, 1, 1;
DELETE FROM #directoryTree
WHERE is_file = 0
OR SQL_file_name NOT LIKE '%.sql'
My next step was to build a cursor, running on my #directoryTree and then to use BULK INSERT as below, but I found out
I cannot use local variable (@sql_files_path). Can one advise the best approach, please?
IF OBJECT_ID('tempdb..#fileContent') IS NOT NULL
DROP TABLE #fileContent;
CREATE TABLE #fileContent(
FIELD1 varchar(1000));
BULK INSERT #fileContent FROM @sql_files_path +'receipt_header.sql'
WITH (FIELDTERMINATOR =' | ',ROWTERMINATOR =' ');
August 25, 2016 at 7:18 am
Something like this applied to your code:
DECLARE @sql_files_path varchar(100) = '\\SomeServer\Somepath\'
DECLARE @BulKInsert nvarchar(300)
SET @BulkInsert = '
BULK INSERT #fileContent FROM ''' + @sql_files_path + 'receipt_header.sql''
WITH (FIELDTERMINATOR ='' | '',ROWTERMINATOR ='' '');'
PRINT @BulKInsert
EXEC sp_executesql @BulkInsert;
Yes, this is a good place for a cursor. There's no way to import a set of files, they must be done one by one and a cursor is the way to go. Be sure to configure it correctly and don't leave the defaults.
August 25, 2016 at 8:05 am
Thank you, Luis
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply