• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 339
  • Last Modified:

DMO: Identity column trashed by bulk copy

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
rmcafee
Asked:
rmcafee
1 Solution
 
odessaCommented:
Try to set "IncludeIdentityValues" to false
and then run "set tablename identity_insert on" query
what's happend
0
 
rmcafeeAuthor Commented:
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
 
gmoriakCommented:
Try running

set tablename identity_insert on

on your source table before the export AND on your destination table before the import.
0
 
bamartinoCommented:
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

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now