Technical FAQs

Ask a Question

How to Quickly Create a Copy of a SQL Table

Issue
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).

Product
Struxureware Power Monitoring
Power Monitoring Expert
ION Enterprise
ION EEM

Environment
SQL Server 2005, SQL Server 2008 R2, SQL Server 2012

Cause
Troubleshooting or correcting data issues.

Resolution
*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 ?