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 [email protected] 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.