2

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

  • 1
    To clarify, you're looking to copy the structure and contents of 3 tables from one database to another? On the same server or different servers? What version of SQL? – BradC Oct 5 '18 at 16:50
  • Yes structure and contents of 3 tables from one to another database on the same server and the version is Sql server 12. Thanks – Michael A. Badejo Oct 5 '18 at 17:37
5

(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.

  • 1
    An additional option for SQL Server 2016 SP2+ is to use DBCC CLONEDATABASE with the VERIFY_CLONEDB option, followed by an INSERT … SELECT into the empty table in the new database. – David Browne - Microsoft Oct 5 '18 at 19:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.