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