buld insert question

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » buld insert question

buld insert question

Rate Topic

Display Mode

Topic Options

Author

Message

rightontarget

Posted Tuesday, February 12, 2013 3:11 PM

Valued Member

Group: General Forum Members
Last Login: 2 days ago @ 9:52 AM
Points: 69, Visits: 213

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,

Post #1419213

opc.three

Posted Tuesday, February 12, 2013 5:46 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:27 PM
Points: 6,735, Visits: 11,788

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1419251

rightontarget

Posted Wednesday, February 13, 2013 12:19 PM

Valued Member

Group: General Forum Members
Last Login: 2 days ago @ 9:52 AM
Points: 69, Visits: 213

Thank you, that worked well for me.

Post #1419688

opc.three

Posted Wednesday, February 13, 2013 12:21 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:27 PM
Points: 6,735, Visits: 11,788

You're welcome. Thanks for the feedback.

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1419690

« Prev Topic | Next Topic »

Permissions