How do I copy the structure and contents of the tables below in SQL Server Management Studio from database
SNO_TMP (on the same server)?
The names of the tables are
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
(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:
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.
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.
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.