Systems Engineering and RDBMS

Back to the Basics: Simple ways of copying data from one table to another

Posted by decipherinfosys on May 29, 2009

A junior DBA at a client site recently asked this question while performing his duties:

“I have 2 tables in the same schema in the database and I need to copy the data from one to the other one.  I typically use DTS (SQL Server 2000) or SSIS (SQL Server 2005) to do that.  Also, at times, I have a need to copy data from multiple tables into a single table for analyzing the summary data.  Is there an easy way to do that using SQL?”

There are two very simple ways of achieving this via T-SQL.

Method 1: One can either create a table (if the destination table does not exist) and then do an:

insert into <new table> select … from <old table or set of tables> where <the where condition over here>

OR

Method 2: If this is something that you need to do as a quick and dirty thing and you do not intend to keep the new table around post the analysis, you can also do:

select … into <new table> from <old table or set of tables> where <the where condition over here>

The “select into” is the same as CTAS in Oracle.  You can read more on that here.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: