I'd like to import data from one SQL Server DB to another. Both DBs have the same table definitions, etc. but different data. The primary keys are auto-incrementing, so the same primary key will refer to different data in both DBs.
I'd need the primary keys from the second DB updated (and, obviously, any foreign keys which reference them) when inserting into the first. I don't care if data is duplicated for now. Is there a way to do this?
Best Answer
You didn't say what version. Since we're talking SQL Server, there are several ways to approach this. If you're on SQL Server 2005/2008, the easiest way is to probably use the Import Data function within SQL Server Management Studio. Actually, what this does is creates an SSIS package that allows you to bring data in to the combined database. This is a simple SSIS package approach, so the foreign keys may be an issue.
Failing this, another option is to use bcp to export the data out of the table(s), however, without the primary/foreign key references. This will put the data in text files. At that point you could use BULK INSERT to bring the data into the combined database. There is a caveat here in that if the columns where the foreign keys are do not allow NULLs, this approach is going to be problematic.
A third option is to write the scripts yourself, and this is fairly easy to do as well. You can take the second database, if it's not already on your SQL Server, and attach it to the SQL Server where the combined database is going to be. A linked server connection can work, too, but it'll be much slower overall. Then you can write your INSERT statements accordingly, leaving off the primary key fields. For tables with foreign key references, you can use JOINs to get the right values for the references, so long as you load the tables in the right order.
A fourth option is to build a custom SSIS package and load the tables in order, and do the lookups accordingly to ensure you have the right foreign key reference values. This is a little more involved than step one, but it'll ensure you'll get it right. Also, if you have to repeat this exercise, you'll already have the package. Another advantage is that it doesn't require you to create a linked server connection, and it'll use the fastest possible insert methods into the combined database.