BCP import exclude identify or some column

Question

I'm using BCP because I want to export some columns from a table:

bcp "SELECT csusUsageDate, csusType, csusTrack1, csusTrack2, csusTrack3, csusDateReaded, csusLoggedIn FROM [DbJamaica].[dbo].[CS_Usage]" queryout "C:\temp\CS_Usage.txt" /U.. /P.. -c -T

Here I exclude my primary key, now I want to import this txt and I want that my primary key will be auto generate:

bcp DbJamaica.dbo.CS_Usage out "C:\temp\CS_Usage.txt" /U.. /P.. -c -T

I have always format not valid why? I also used format file but I have the same error. This is my question: how to exclude identify column or some columns?

Answer 1

You can't, as far as I know. Bcp basically bulk inserts blindly into a table, if the columns don't match, you get an error. What you can do, though, is create a staging table like:

SELECT  TOP 0 csusUsageDate, csusType, csusTrack1, csusTrack2, csusTrack3, csusDateReaded, csusLoggedIn
INTO    [DbJamaica].[dbo].[CS_Usage_TEMP] 
FROM    [DbJamaica].[dbo].[CS_Usage]

Then you can use bcp on your staging table:

bcp DbJamaica.dbo.CS_Usage_TEMP out "C:\temp\CS_Usage.txt" /U.. /P.. -c -T

Next you can insert the data from the staging to the actual table, where you set the PK column as IDENTITY:

INSERT INTO DbJamaica.dbo.CS_Usage (csusUsageDate, csusType, csusTrack1, csusTrack2, csusTrack3, csusDateReaded, csusLoggedIn)
SELECT   csusUsageDate, csusType, csusTrack1, csusTrack2, csusTrack3, csusDateReaded, csusLoggedIn
FROM     DbJamaica.dbo.CS_Usage_TEMP

And finally cleanup:

DROP TABLE DbJamaica.dbo.CS_Usage_TEMP