Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Here's the thing, I have been running identical copies of my website for two different geographical regions. The site now supports multi-currency pricing so I want to merge both sites together.

All tables that I need to merge are Identity based and foolishly the matching tables in each copy of the database were seeded at the same number.

Is there a way to copy the data from one database to another at the same time giving the copy data new identity values that also are reflected in the foreign key values of referenced tables?

i.e.

Account
- Id
- Email
- FirstName
- etc

AccountImage
- Id
- Account_id
- FileName
- etc

And so on..

share|improve this question
1  
do you have foreign key constraints in place and how are they setup? –  Edward Dortland Jan 30 at 13:09
1  
Go with SSIS and select keep identity. This will have the flexibility to schedule it using SQL agent and even when you move to 2012. –  Kin Jan 30 at 15:11
add comment

3 Answers

Unless I'm missing something your problem is not moving the data it's dealing with the identity values that are already set up. If that is the case then try this.

  1. Pick a value greater than your current ident values on either DB. I would pick a round value, say 1,000,000.
  2. Pick the ident values you want to change (for example if you have lookup tables that are the same for both DBs then you probably want to leave them the same.)
  3. When you move your data add the value you selected in step 1 to the idents value you want to change as you move them.

As long as you are consistent in the value you are adding all of your relationships will stay the same into the combined DB. So for example

You have an employee table with a current max id of 200,000 in DB A and 1,400,000 in DB B. You decide to move the data from DB A to DB B because it means moving less data. As you move your employee table you add 2,000,000 to the Employee_Id column.

USE DatabaseB
GO

SET IDENTITY_INSERT Employee ON

INSERT INTO Employee (Employee_Id, Other_Columns)
SELECT Employee_Id + 2000000, Other_Columns
FROM DatabaseA.dbo.Employee

SET IDENTITY_INSERT Employee OFF
GO

And last piece of advice back up everything before you start in case you make a mistake :)

share|improve this answer
add comment

IF both databases same server and you have permission to access both database

Method 1:

You can script entire main database and run it with new database name, then you will have same schema and data of main database (Possible from SQL server 2008 Version onwards)

Method 2:

If you need only few tables with schema,relationships and data then scripts it from main database

Follow table hierarchy - 1.Master table 2.Child table

  1. Script tables schema
  2. Script table data
  3. Script table relationships and constraints

Run it in same order

Method 3:

If you need only tables schema and data then go for IMPORT/EXPORT

share|improve this answer
    
Thanks, both databases are on the same instance of Sql Server so that's no problem. The server is 2008 R2, though I want to upgrade to 2012 soon. In the Generate Scripts tool, do I select Tables, then Data Only? Which option do I change to force the script to create new identities for everything, 'Script unique keys', 'Script primary keys' ? –  Jordan Jan 30 at 13:28
    
I only need the data as the schemas are identical. –  Jordan Jan 30 at 13:30
add comment

Perhaps the easiest way is to just do a insert...select, re-referencing at the same time by using set identity_insert on and adding a fixed value to the existing identity to ensure no conflict between the data sets ?

Something like this:

set identity_insert myTable on

insert into db1.dbo.myTable
( identity_column, [column, column...] )
select identity_column+1000, [column, column...]
from db2.dbo.myTable

set identity_insert myTable off
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.