SQL Server Generate Script To Fill Tables With Data From Other Database?

Question

Let's say I have two databases with identical tables, but one database's tables contains data while the other doesn't. Is there a way in SQL Server to generate a script to fill the empty tables with data from the full tables?

Answer 1

If the tables are identical and don't use an IDENTITY column, it is quite easy.

You would do something like this:

INSERT INTO TableB 
SELECT * FROM TableA

Again, only for identical table structures, otherwise you have to change the SELECT * to the correct columns and perform any conversions that are necessary.

Answer 2

And, to add to the @WilliamD answer, if there is an IDENTITY column you can use a variation of the INSERT statement.

Assuming you have two columns (Col1 and Col2, with Col1 having IDENTITY property) in the tables, you can do the following:

SET IDENTITY_INSERT TableB ON

INSERT INTO TableB (col1, col2)
SELECT col1, col2 FROM TableA

SET IDENTITY_INSERT TableB OFF

It's necessary to list the columns in this situation.