CODEWITHSUNDEEP
sqlsql-server
sooprise
sooprise

Reputation: 23187

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

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?

Upvotes: 2

Views: 1342

Answers (2)

bobs
bobs

Reputation: 22184

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.

Upvotes: 3

sql_williamd
sql_williamd

Reputation: 441

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.

Upvotes: 3

Related Questions