SQL to copy a table from one Database to Another

You might need to copy tables from one database to another.

I do this often if I want to test complicated scripts that update tables. You might have data in one table that you need to use to compare to another table and run updates accordingly.

A quick way to test this is to copy the tables to a Development Database and then just run the scripts against the tables in there.

Let’s consider we have DB1 with Table1 and Table2

We want to copy Table1 and Table2 to a Database called DevDB1 (assuming default dbo schema)

Use the following commands to do this.

SELECT * 
INTO DB1.dbo.Table1
FROM DevDB1.dbo.Table1

SELECT * 
INTO DB1.dbo.Table2
FROM DevDB1.dbo.Table2

Not this will create the destination table and structure and copy the data only.

It will not create constraints, defaults or indexes.

For that, you would need to pre-create the tables with a script and then just insert the data with the same command.