SQL Server – Copy a table into new table with/without data

Copy only the structure of an existing table into new table:

SELECT * INTO tblNew FROM tblOld WHERE 1=2

The above query will copy the structure of  an existing table(tblOld) into the new table(tblNew).

Copy only the structure with data of an existing table into new table:

SELECT * INTO tblNew FROM tblOld

This is also same like the previous query, but it copies the structure of existing table(tblOld) with data as well into the new table(tblNew).

Use SQL Server Management Studio to clone a database

There are a number of ways to clone a MS SQL Server database. The below is one way using MS SQL Server Management Studio.

1. Right click database > Tasks > Back Up..
2. Select Backup type: full
3. Select Destination: Disk
4. Click Add and select the location
5. Click Ok to run the backup.
6. Right click Databases node > Restore Database
7. Select Device and browse for database backed up above.
8. Enter the name of the database to create in the destination
9. Select the files tab
10. Click the … button at the end of both the mdf and ldf lines and pick a new name. Don't leave the default as this will overwright the original database.
11. If you are replacing an exisitng database select the 'Overwrite the exisitng database' options on the Options tab.
12. Click Ok

Clone a table using SQL Management Studio

Open a new query window and enter the following SQL statement.

SELECT * INTO Party_Previous_Results
FROM Party

Change NewTable to the name that the new table should have, change OldTable to the name of the current table

This will copy over the basic table structure and all the data…it will NOT do any of the constraints, you need to script those out and change the names in those scripts.

Export SQL Server Database Schema and Data

In SQL Server Management Studio right-click your database and select Tasks / Generate Scripts. Follow the wizard and you'll get a script that recreates the data structure in the correct order according to foreign keys. On the wizard step titled "Set Scripting Options" choose "Advanced" and modify the "Types of data to script" option to "Schema and data"

Remember server connections in MS SQL Server Management Studio

When exiting MS SQL Server Management Studio connections to existing servers are lost. Next time you open Management Studio each server has to me manually re-enter your information for each server.

However there is a solution. Open Management Studio goto View > Registered Servers. Right click Local Server Groups and select New Server Registration and fill in your server details. Next time you open Management Studio double click the server entry in the Registered Servers window to reconnect.