Browse FAQs

How to Create a Copy of a SQL Table

Published date: 29 June 2020

Often when troubleshooting and\or correcting data issues, it is desirable to work on a temporary or subset of the production data
to in order to test a solution or to focus on a small portion of some particular data item(s).

Struxureware Power Monitoring
Power Monitoring Expert
ION Enterprise

SQL Server 2005, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Server 2016

Troubleshooting or correcting data issues.

*Warning: Irreparable database damage can occur. This procedure should only be performed by users familiar with SQL Server Management Studio. Databases should be backed up prior to performing this procedure.*

An easy way to create a copy of a table is to use a SQL command.

Use SELECT INTO to extract all or a subset of the rows or from an existing table into the new table.
The new table must not exist already.

The following example will copy the Source table under the IonNetwork schema to a new table called MySource under the Test schema:
SELECT * INTO Test.MySource FROM IonNetwork.Source

You can also create the new table from a specific subset of rows in the original table.
In this case, you would append a Where clause to specify the criteria for only those rows you are interested in.
SELECT * INTO Test.MySource FROM IonNetwork.Source
Where Name like '%MySources%'

Additionally,  you could create the new table from a specific subset of columns in the original table.
In this case, you specify the names of the columns to copy after the SELECT keyword.
Any columns not specified are excluded from the new table.

The following example copies specific columns to a new table:
SELECT ID, Name, SourceTypeID, Description INTO Test.MySource
FROM IonNetwork.Source

Was this helpful?

What can we do to improve the information ?

Can't find what you are looking for?

Reach out to our customer care team to receive information on technical support, assistance for complaints and more.