Post reply

Bulk Inserts using a variable

  • November 19, 2013 at 2:49 pm

    #300671

    I am trying to set a path ... that is loaded from a table.

    So I tried just to use a variable in the Bulk Insert, but I am getting Incorrect syntax near '@From_Path'.

    declare @From_Path as varchar (max)

    select @From_Path = '\\myserver\myshare\load.dat'

    create table #Import_Load_dat ( t varchar(1000) )

    bulk insert #Import_Load_dat from @From_Path

    What am I doing wrong?

  • November 19, 2013 at 2:55 pm

    #1667601

    you have to switch to dynamic SQL for the whole thing, when you try to parameterize BULK INSERT; it only accepts static strings, and not variables.

    DECLARE @sql varchar(8000)

    --bulk insert won't take a variable name, so make a sql and execute it instead:

    set @sql = 'BULK INSERT BULKACT FROM ''' + @path + @filename + ''' '

    + ' WITH (

    DATAFILETYPE = ''char'',

    FIELDTERMINATOR = '','',

    ROWTERMINATOR = ''\n'',

    FIRSTROW = 2

    ) '

    print @sql

    exec (@sql)

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • November 20, 2013 at 6:43 am

    #1667782

    Lowell,

    Thanks for your help.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply