How can I use INSERT INTO to copy an entire table into another?

How can I use INSERT INTO to copy an entire table into another?

If the tables share the same structure (same number of columns, each matching pair of columns with the same datatype), then the syntax is really simple --

insert
   into table2
 select *
   from table1

If the tables do not share the same structure, then you must name the columns of the table you are inserting into, and provide an expression for each of them in the subselect --

insert
   into table2
      ( col1
      , col2
      , col3 )
 select field3
      , ( field7 + field8 ) * 9.37
      , 0
   from table1

INSERT INTO is great because of this ability to reformat data as part of the insertion process.

For More Information

• What do you think about this answer? E-mail us at editor@searchDatabase.com with your feedback.
• The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
• The Best SQL Web Links
• Have a SQL tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
• Ask your technical SQL questions--or help out your peers by answering them--in our live discussion forums.
• Ask the Experts yourself: Our SQL guru is waiting to answer your technical questions.