Post reply

BULK INSERT from multiple objects

  • August 25, 2016 at 6:53 am

    #329307

    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

    #1897457

    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.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • August 25, 2016 at 8:05 am

    #1897475

    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