CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Jammin
Jammin

Reputation: 3100

Copy data only between two databases

Im trying to copy data only between two SQL server 2008 databases. I need to keep the existing stored procs and functions intact and copy data only. The DB schemas are identical but im running into issues with PK's.

I first tried:

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
EXEC sp_MSForEachTable 'DELETE FROM ?'

To remove all data. But get 

Failure inserting into the read-only column

So i then tried to set IDENTITY_INSERT ON across all tables with:

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
EXEC sp_MSForEachTable 'DELETE FROM ?'
EXEC sp_MSForEachTable  'ALTER TABLE ? SET IDENTITY_INSERT ON'

with no luck.

What is the best way to export data only between two databases, leaving the original procs and functions intact?

Thanks.

Edit: Im using SQL Export to copy the data from source to destination. I need to keep the destinations DBs procs and functions, just copy the data only.

Upvotes: 0

Views: 1323

Answers (5)

Mark Broadbent
Mark Broadbent

Reputation: 411

Hi in order to get around your issues with your constraints, please read this blog post I wrote on the subject.

http://tenbulls.co.uk/2009/07/22/checking-your-constraints-to-check-your-integrity/

Upvotes: 0

Jammin
Jammin

Reputation: 3100

I went with a varation of both answers this in the end. I used a 3rd database as a temp database.

1)I did a full back up of the database i needed the data from (live)
2)I restored this backup to my temp database.
3)I scripted the database i needed the procs and functions from, only scripting procs and funcs and using DROP and IF INCLUDES.
4)I ran the script from #3 against my temp database giving the data from DB1 and the procs and funcs from DB2
5)I restored DB2, using OVERWRITE from a backup of my temp database.

Thanks guys id mark all as correct if I could.

Upvotes: 0

Alberto Martinez
Alberto Martinez

Reputation: 2670

The error you are getting doesn't seems like a PK violation or an Identity issue. I see two possible causes:

  1. If you are getting the error when trying to insert the data, I would check if the tables have any computed columns. Many programs fail to take them into account when exporting data, and include the computed columns in the insert column list.

  2. If you are getting that error in the delete step, probably you have a trigger that fires on delete, and it try to insert data and fails for some reason (the idea of these triggers is maintain a copy of the deleted data in another location). If that is the case, fix the insert or just disable the trigger.

Upvotes: 1

Lill Lansey
Lill Lansey

Reputation: 4915

Just remove the identity specification from all the table pkeys in the second db.

What is likely happening here is that you have pkey as an identity column in both dbs, and it makes sense to do so in the first, but you cant copy its value into another identity column.

You wouldn't want the pkey as an identity pkey in the second db anyway, then, all your foreign keys wouldn't work.

Upvotes: 2

Cahit
Cahit

Reputation: 2534

I would probably approach it from a different angle: by scripting all objects via SQL Enterprise Manager into a file and running this file on a blank database. This way, you'll have all metadata but no actual data in the second database, and you can use it for additional copies in the future.

Upvotes: 1

Related Questions