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?

  • I'm not 100% clear on what you're trying to do with this import: Is there an overlap (the same data in both places, and you don't want to overwrite what's in SQL Server), a key collision (different data with the same keys, and you want to import with a new key), or some mix of the two? – voretaq7 Jul 13 '10 at 17:36
  • @voretaq7: Key collision, see updated question. I've removed references to Access to make it more accessible (heh) – BlueRaja Jul 13 '10 at 18:00

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.

  • How can you automatically update foreign keys in a data flow in SSIS? – BlueRaja Jul 14 '10 at 19:07
  • Before you put to the destination, you can do a lookup and populate a column accordingly. This is a common practice in SSIS. – K. Brian Kelley Jul 14 '10 at 19:11
  • SSIS is doing the trick; it's a great tool, thanks! – BlueRaja Jul 20 '10 at 18:55

OK, since you've got what sounds like mostly disjoint data (and don't care if you get some duplicates anyway) there's a workaround that I've used in Postgres which may be applicable:

  1. Dump the data as SQL commands
    (A series of INSERT statements or something else text-like and friendly to edit)
  2. Edit the dump and remove the primary keys
    (An editor that understands regular expressions can be helpful here, but not strictly necessary as long as it can repeat an action for each line in a file)
  3. Load the dump into your SQL Server DB
    (If the keys are auto-incrementing and auto-filling like a SERIAL key in Postgres you should get new keys for each row)

This is all predicated on their being an easy way to dump your data in that format (or writing a quick PHP/ASP/whatever script to generate the SQL could work too...). Also note that this technique gets a bit unwieldy with huge data sets -- The limiting factor is usually the text editor choking on an enormous file.

  • How do you update foreign keys? – BlueRaja Jul 13 '10 at 18:27
  • ahhh, there are foreign keys? That changes the game! The only way I can think of to get around that is to add some offset (10000, 100000, etc.) to the keys in your old DB so they don't overlap (UPDATE ... CASCADE them so the tables pointing at it get the new values), and then update the serial primary key generator to only generate keys after the largest key in your DB (That's a crappy solution, but it's the best I got :-/) – voretaq7 Jul 13 '10 at 18:39
  • With bcp you can specify the fields to extract and the data file will be condensed (not a series of INSERT statements). This is a more optimal way to go if you want to extract the data to text files. – K. Brian Kelley Jul 13 '10 at 19:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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