{"id":515,"date":"2018-05-13T23:03:14","date_gmt":"2018-05-13T16:03:14","guid":{"rendered":"http:\/\/www.sqlservertutorial.net\/?page_id=515"},"modified":"2021-11-06T12:13:55","modified_gmt":"2021-11-06T05:13:55","slug":"sql-server-insert-into-select","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-insert-into-select\/","title":{"rendered":"SQL Server INSERT INTO SELECT"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQL Server INSERT INTO SELECT<\/code> statement to add data from other tables to a table.<\/p>\n\n\n\n

Introduction to SQL Server INSERT INTO SELECT statement #<\/a><\/h2>\n\n\n\n

To insert<\/a> data from other tables into a table, you use the following SQL Server INSERT INTO SELECT<\/code> statement:<\/p>\n\n\n

INSERT<\/span>  [ TOP ( expression ) [ PERCENT<\/span> ] ] \nINTO<\/span> target_table (column_list)\nquery<\/span>\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, the statement inserts rows returned by the query<\/code> into the target_table<\/code>.<\/p>\n\n\n\n
The TOP<\/code> clause part is optional. It allows you to specify the number of rows returned by the query to be inserted into the target table. If you use the PERCENT<\/code> option, the statement will insert the percent of rows instead. Note that it is a best practice to always use the TOP<\/code> clause with the ORDER BY<\/code><\/a> clause.<\/p>\n\n\n\n
SQL Server INSERT INTO SELECT examples #<\/a><\/h2>\n\n\n\n
Let’s create a table<\/a> named addresses<\/code> for the demonstration:<\/p>\n\n\n
CREATE<\/span> TABLE<\/span> sales.addresses (\n    address_id INT<\/span> IDENTITY<\/span> PRIMARY KEY<\/span>,\n    street VARCHAR<\/span> (255<\/span>) NOT<\/span> NULL<\/span>,\n    city VARCHAR<\/span> (50<\/span>),\n    state VARCHAR<\/span> (25<\/span>),\n    zip_code VARCHAR<\/span> (5<\/span>)\n);   \n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
1) Insert all rows from another table example #<\/a><\/h3>\n\n\n\n
The following statement inserts<\/a> all addresses from the customers<\/code> table into the addresses<\/code> table:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> sales.addresses (street, city, state, zip_code) \nSELECT<\/span>\n    street,\n    city,\n    state,\n    zip_code\nFROM<\/span>\n    sales.customers\nORDER<\/span> BY<\/span>\n    first_name,\n    last_name; \n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
To verify the insert, you use the following query:<\/p>\n\n\n
SELECT<\/span>\n    *\nFROM<\/span>\n    sales.addresses;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the result:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
2) Insert some rows from another table example #<\/a><\/h3>\n\n\n\n
Sometimes, you just need to insert some rows from another table into a table. In this case, you limit the number of rows returned from the query by using conditions in the WHERE<\/a><\/code> clause.<\/p>\n\n\n\n
The following statement adds the addresses of the stores located in Santa Cruz<\/code> and Baldwin<\/code> to the addresses<\/code> table:<\/p>\n\n\n
INSERT<\/span> INTO<\/span> \n    sales.addresses (street, city, state, zip_code) \nSELECT<\/span>\n    street,\n    city,\n    state,\n    zip_code\nFROM<\/span>\n    sales.stores\nWHERE<\/span>\n    city IN<\/span> ('Santa Cruz'<\/span>, 'Baldwin'<\/span>)\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SQL Server returned the following message indicating that two rows have been inserted successfully.<\/p>\n\n\n
(2 rows affected)\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
3) Insert the top N of rows #<\/a><\/h3>\n\n\n\n
First, you use the following statement to delete all rows from the addresses<\/code> table:<\/p>\n\n\n
TRUNCATE<\/span> TABLE<\/span> sales.addresses;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, to insert the top 10 customers sorted by their first names and last names, you use the INSERT TOP INTO SELECT<\/code> statement as follows:<\/p>\n\n\n
INSERT<\/span> TOP (10<\/span>) \nINTO<\/span> sales.addresses (street, city, state, zip_code) \nSELECT<\/span>\n    street,\n    city,\n    state,\n    zip_code\nFROM<\/span>\n    sales.customers\nORDER<\/span> BY<\/span>\n    first_name,\n    last_name;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SQL Server returned the following message showing that ten rows have been inserted successfully.<\/p>\n\n\n
(10 rows affected)\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
4) Insert the top percent of rows #<\/a><\/h3>\n\n\n\n
Instead of using an absolute number of rows, you can insert a percent number of rows into a table.<\/p>\n\n\n\n
First, truncate<\/a> all rows from the addresses<\/code> table:<\/p>\n\n\n
TRUNCATE<\/span> TABLE<\/span> sales.addresses;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, insert the top 10 percent of rows from the customers<\/code> table sorted by first names and last names into the addresses<\/code> table:<\/p>\n\n\n
INSERT<\/span> TOP (10<\/span>) PERCENT<\/span>  \nINTO<\/span> sales.addresses (street, city, state, zip_code) \nSELECT<\/span>\n    street,\n    city,\n    state,\n    zip_code\nFROM<\/span>\n    sales.customers\nORDER<\/span> BY<\/span>\n    first_name,\n    last_name;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
SQL Server issued the following message indicating that 145 rows have been inserted successfully.<\/p>\n\n\n
(145 rows affected)\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this tutorial, you have learned how to use the SQL Server INSERT INTO SELECT<\/code> statement to insert rows from other tables into a table.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful?<\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t
The query<\/code> is any valid SELECT<\/a><\/code> statement that retrieves data from other tables. It must return the values that are corresponding to the columns specified in the column_list<\/code>.<\/p>\n\n\n\n