How to Copy Structure and Contents of Tables Between Databases in SQL Server

sql serversql-server-2012ssms

How do I copy the structure and contents of the tables below in SQL Server Management Studio from database SNO_TEST to SNO_TMP (on the same server)?

The names of the tables are ADMIN_ErrorLog, AdminMissingFiles and SNO_QAErrorCategory

Best Answer

(I would have assumed there was already a question on this, but I couldn't find a general one for SQL Server, just narrowly tailored ones, or ones for other platforms.)

The method I chose would probably depend on how big the tables are, and also whether there are any other objects associated with the tables (like indexes, triggers, constraints, etc.). But here are a few methods that might work:

  1. Use the "Import/Export Wizard".

This is probably the simplest. Right click the source database and go to Tasks > Export Data... I'm not going to walk through all the steps of the wizard here, but you can select the source and destination databases, then choose which tables you want to copy. Here's a page that walks through a simple example, although they are using it for importing data from Excel.

After copying the data, add any necessary indexes/constraints/etc.

  1. Do a SELECT INTO

With very small tables, I'll just do a very quick:

SELECT *
INTO myDestinationDB.dbo.tablename
FROM mySourceDB.dbo.tablename

Replacing with your database names and table names, of course.

This creates the destination tables on the fly. You'll need to add indexes/constraints yourself afterwards. Also be aware that this won't always work for some limited situations like identity columns.

  1. Script out the table definition, then do an INSERT INTO

Right-click the table in the source database, and choose "Script Table as > Create To > New Query Editor Window"

Then change the USE databasename at the top to your destination database, and run the CREATE statement to create the table. Now is your chance to also add indexes or constraints, if necessary. Then to copy the data, do something like:

INSERT INTO myDestinationDB.dbo.tablename
SELECT * FROM mySourceDB.dbo.tablename

If these tables have millions and millions of rows, definitely use the 1st method above, or use an advanced technique like a custom SSIS package (not covered in this answer), otherwise you might fill up your transaction log.

Hope this leads you in the right direction. Good luck.

Related Question