I am diving into using bcp, must have been over 10 years since I last used it.
I want to use the TABLOCK hint for loads, I assume I want to use a format file so I can automate the export/import process?
Source table has an identity filed which I want to discard, Destination table has an identity field which I want to use. We have up to 4 source servers containing the same data, data from these 4 different tables will be aggregated onto the destination table.
To tackle the identity destination table issue do I just create a format file like this?
I think I want to use native format for best performance, do I change SQLCHAR to NATIVE? or is native only used by using the -n switch? How can I do what I need to do (wrt Identity values) by using the -n switch? Would I need to import into a dummy table and then move the data to the real table? (which I would prefer not to have to do since there is a lot of data involved)
Actually, it looks to be remarkably simple to achieve. The Identity values are controlled on the way in by the -E switch. If you leave that off, then the target table generates its own identity values (at least in SQL 2008. I have not tested earlier versions). i created a simple table like this:
create table test1
(id int identity(1, 1) not null primary key,
insert into test1 (col2) values ('hello')
BCP'd the data out
bcp applications..test1 out test.dat -n -Sservername -T
then BCP'd the data back in to the same table.
bcp applications..test1 in test.dat -n -Sservername -T
Here is the result:
(2 row(s) affected)