469,924 Members | 1,529 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,924 developers. It's quick & easy.

BCP import then export


I'm not a SQL person but have the need to export a tables content (from live) and then import into a test system. A backup and restore is not an option.

bcp Database..databasetable out "c:\Documents and Settings\Administrator\My Documents\test3.txt" -c -P

Exports ok (I would like to export the tables column headers aswell), but if I try to reimport this file on the same server using

bcp Database..databasetable in c:\Documents and Settings\Administrator\My Documents\test3.txt" -S(local) -c -P password

but I get the following error.....

Starting copy...
SQLState = 23000, NativeError = 2627
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY
constraint 'PK___2__10'. Cannot insert duplicate key in object 'ComputerEntry'.
SQLState = 01000, NativeError = 3621
Warning = [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been

BCP copy in failed

C:\Documents and Settings\Administrator\My Documents>Pause
Press any key to continue . . .

Why does export and import fail on the same server (I'm testing on my test system)

I'm afraid this error means little and the more I search google the more confused I get !!

Thanks inadvance

Feb 24 '08 #1
3 5380
8,127 Expert 4TB
The error message suggests you are importing data to a table and by this process making some primary key violation. ie--trying to insert a duplicate value into the table.
Feb 25 '08 #2
759 Expert 512MB
Hey, You had exported the values first time and the second time you are trying to insert duplicate values and that violates key.

Truncate the table and then try...
Feb 25 '08 #3
2,878 Expert 2GB
The rows that you just exported is still on the table. When you BCP-in, it will not delete those existing rows and just add the ones you just imported. Which makes everything a duplicate. Since the table has a duplicate primary key constraint/check, maybe through an index, it gives you the error.

-- CK
Feb 25 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by news | last post: by
10 posts views Thread by Niklas | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.