Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

buld insert question Expand / Collapse
Author
Message
Posted Tuesday, February 12, 2013 3:11 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 1:13 PM
Points: 100, Visits: 338
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
Posted Tuesday, February 12, 2013 5:46 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:34 AM
Points: 7,098, Visits: 12,606
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
Post #1419251
Posted Wednesday, February 13, 2013 12:19 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 1:13 PM
Points: 100, Visits: 338
Thank you, that worked well for me.
Post #1419688
Posted Wednesday, February 13, 2013 12:21 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:34 AM
Points: 7,098, Visits: 12,606
You're welcome. Thanks for the feedback.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1419690
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse