Import CSV to SQL Table using BCP cmd utility

Question

My source CSV is comma delimited.

Example

Col1, Col2, Col3

Destinatin SQL Table

Col1, Col2, Col3, Col4, Col5 ( Col4 and Col5 are like audit columns)

So when i do bcp import can these Col4 and Col5 set to some default values or should I have to massage the data / source file to include these before i execute bcp cmd.

Which is preferred way.

Use SSIS to read source / derive audit columns and map to destination.

or any other right way.

Cᴏʀʏ · Accepted Answer · 2012-03-08 00:14:03Z

3

I think you have a couple of options:

Change the schema of your destination table to include DEFAULT constraints on the two columns that will not get updated with values from your import file (and then remove them later if you don't need them
Build an SSIS package -- it would probably take about the same amount of time as it would take you searching the Interweb for a solution using bcp
Try using BULK INSERT through sqlcmd.exe or SQL Server Management Studio and adding in default values (SELECT c1, c2, c3, 'My default', GETDATE() FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)}; DefaultDir=C:\Path\To\Folder\With\CSV;','SELECT * FROM MyCsv.csv'))

answered Mar 8, 2012 at 0:14

Cᴏʀʏ

108k20 gold badges169 silver badges201 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Import CSV to SQL Table using BCP cmd utility

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related