Post reply

buld insert question

  • February 12, 2013 at 3:11 pm

    #270802

    Hi all,

    I have a text file that has one number in it. I need to load that number into the table, but I need to add 2 more fields to it (hard-code value and the current date).

    For instance: my file has 12345 in it. I need to do the following:

    insert into tableA

    values ('A',12345,getdate());

    Is it possible to do in one shot in:

    BULK INSERT tableA

    FROM 'c:\temp\file.txt'

    WITH ( ROWTERMINATOR ='' )

    Thanks,

  • February 12, 2013 at 5:46 pm

    #1587250

    SSIS would be my choice for this. A Derived Column Transform after reading the file could append your additional columns before loading the row into a table.

    If you want to stay in T-SQL then you can use OPENROWSET and read the entire file as a SINGLE_CLOB and append the columns you want to the resultset:

    SELECT BulkColumn AS LineFromFile,

    'something' AS Col1,

    'something else' AS Col2

    FROM OPENROWSET(BULK N'P:\@\1.txt', SINGLE_CLOB) AS Document;

    edit: spelling

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • February 13, 2013 at 12:19 pm

    #1587612

    Thank you, that worked well for me.

  • February 13, 2013 at 12:21 pm

    #1587614

    You're welcome. Thanks for the feedback.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

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

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