To add values into a table from one or more other tables, use a SELECT clause in the INSERT statement. The SELECT clause can insert values into some or all columns in a row.
Inserting values into only some columns can come in handy when you want to take some values from an existing table. Then you can use UPDATE to add the values for other columns.
Before inserting values for some (but not all) of the columns in a table, be sure that a default exists or NULL has been specified for the columns in which you are not inserting values. Otherwise, you will get an error message.
When you insert rows from one table into another, the two tables must have compatible structures ¾ that is, the relevant columns must be of the same datatype or have datatypes that SQL Server automatically converts between.
If the columns in both tables are in the same order in their CREATE TABLE statements, you don't need to specify column names in either table. Suppose that the newauthors table contains some rows of author information in the same format as the authors table. This example adds all the rows in newauthors to authors:
INSERT authors SELECT * FROM newauthors
However, because the columns in the two tables are likely not listed in the same sequence in their respective CREATE TABLE statements, you can use either the INSERT or the SELECT statement to order the columns so that they match.
For example, say that the CREATE TABLE statement for the authors table contains the columns au_id, au_fname, au_lname, and address in that order, and the newauthors table contains au_id, address, au_lname, and au_fname. You must make the column sequence match in the INSERT statement. You can do this in either of these two ways:
INSERT authors (au_id, address, au_lname, au_fname) SELECT * FROM newauthors
Or
INSERT authors SELECT au_id, au_fname, au_lname, address FROM newauthors
If the column sequence in the two tables fails to match, SQL Server cannot complete the INSERT operation or completes it incorrectly, putting data in the wrong column. For example, you might get address data in the au_lname column.
You can use computed columns in a SELECT statement inside an INSERT statement. For example, imagine that a table named tmp contains some new rows for the titles table with some out-of-date data (the price figures need to be doubled). A statement that would increase the prices and insert the tmp rows into titles looks like this:
INSERT titles SELECT title_id, title, type, pub_id, price * 2, advance, royalty, ytd_sales, notes, pubdate FROM tmp
When you perform computations on a column, you cannot use the SELECT * syntax. Each column must be named individually in the select list.
You can insert data into a table based on other data in the same table. Essentially, this means copying all or part of a row.
For example, you can insert a new row in the publishers table that is based on the values in an already existing row in the same table. (Be sure you follow the rule on the pub_id column for legal entries.) Here's how:
INSERT publishers SELECT '9980', 'test', city, state, country FROM publishers WHERE pub_name = 'New Moon Books' (1 row affected) SELECT * FROM publishers
pub_id |
pub_name |
city |
state |
country |
------ |
----------------------- |
----------- |
----- |
-------- |
0736 |
New Moon Books |
Boston |
MA |
USA |
0877 |
Binnet & Hardley |
Washington |
DC |
USA |
1389 |
Algodata Infosystems |
Berkeley |
CA |
USA |
1622 |
Five Lakes Publishing |
Chicago |
IL |
USA |
1756 |
Ramona Publishers |
Dallas |
TX |
USA |
9901 |
GGG&G |
München |
(null) |
Germany |
9952 |
Scootney Books |
New York |
NY |
USA |
9999 |
Lucerne Publishing |
Paris |
(null) |
France |
(8 row(s) affected)
Similar to the INSERT...SELECT capability, INSERT INTO allows you to create a new table entirely and populate it with the results set of the SELECT statement. For details, see the INSERT statement in the Microsoft SQL Server Transact-SQL Reference.