Post reply

TSQL Concatenate Error

  • August 3, 2012 at 1:17 pm

    #260082

    What is wrong with the following code?

    BULK INSERT test

    FROM 'myfile_'+ CONVERT(VARCHAR(20), GETDATE(), 112) + '.TXT'

    WITH

    (FIRSTROW = 2,

    FIELDTERMINATOR = '~',

    ROWTERMINATOR = '')

    Thanks

  • August 3, 2012 at 1:22 pm

    #1521600

    Whats the error?

  • August 3, 2012 at 1:30 pm

    #1521605

    BULK insert does not allow variables , nor will it allow concatention to create the file name;

    BULK INSERT BULKACT FROM 'c:\Export_o.txt' --valid

    BULK INSERT BULKACT FROM 'c:\' + @path --error

    BULK INSERT BULKACT FROM @path --error

    so you have to switch to dynamic SQL in order to do what you are trying to do:

    --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 = ''\&#110'',

    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!

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

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