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.