Solved

DMO: Identity column trashed by bulk copy

Posted on 1998-10-15
4
331 Views
Last Modified: 2008-01-09
I'm using the SQL-DMO BulkCopy object in VB 5 to transfer data between servers. It is important to maintain the existing identity values since they are referenced by other tables, so I set the BulkCopy.IncludeIdentityValues property to "true" on both the export and import sides, but the identity values are regenerated by SQL Server nonetheless when the import is done.  I also tried running the "set tablename identity_insert on" query before doing the import, which normally would allow explicit insertion of identity values, but it made no difference.  Any ideas?
0
Comment
Question by:rmcafee
4 Comments
 
LVL 2

Expert Comment

by:odessa
ID: 1090596
Try to set "IncludeIdentityValues" to false
and then run "set tablename identity_insert on" query
what's happend
0
 

Author Comment

by:rmcafee
ID: 1090597
OK, I tried all 4 permutations of BulkCopy.IncludeIdentityValues = true/false and Database.Execute("set identity_insert tablename on/off").

The resulting exported data file doesn't have any identity values in it, regardless of what I do with these flags.
0
 
LVL 3

Accepted Solution

by:
gmoriak earned 100 total points
ID: 1090598
Try running

set tablename identity_insert on

on your source table before the export AND on your destination table before the import.
0
 

Expert Comment

by:bamartino
ID: 1090599
Use the option
-E with bcp (explicit identity)
when loading data, this will allow you to keep the identity column as it comes with the file.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

778 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question