CODEWITHSUNDEEP
c#sql-serverdatabasessis
Nauman Sharif
Nauman Sharif

Reputation: 205

how to move data from one database to other without SSIS package

i have two database A,B with same number of tables and schema in sql server. All tables contain autogenerated PK, both databases contain their own data.

Now i have to move all data of A to B. because i want to use only one database in future

i tried to move them with SSIS packages but it copied data with new PK. so where these keys are using as FK lost there reference and data is going to corrupt.

Please help me out, how i can move data with there all constraint and PK,FK consistency.

Upvotes: 3

Views: 571

Answers (3)

Registered User
Registered User

Reputation: 8395

If you have an identity column and you want to maintain the values as you move them from an old table to a new table, then check "Keep Identity" in your data flow destination. If you are moving the data between databases on different servers, then SSIS is a perfectly adequate tool for this purpose. If you are moving data between databases on the same server, then you can turn on identity insert for your current session, insert the records in the new table, and then turn off identity insert. There's no reason you should have to lose your current key values.

Upvotes: 1

Paddy
Paddy

Reputation: 33867

You might want to have a little look at this tool (or at least the trial of it). Very good in my experience:

http://www.red-gate.com/products/sql-development/sql-data-compare/

Upvotes: 1

Randy Minder
Randy Minder

Reputation: 48482

One simple way to do it, without SSIS, is to have SSMS (SQL Server Management Studio) script out the entire database, including both schema and data, and then run the script against the target database. I have done this many times, and it works quite well.

Upvotes: 1

Related Questions