{"id":1669,"date":"2019-04-13T18:03:39","date_gmt":"2019-04-13T11:03:39","guid":{"rendered":"http:\/\/www.sqlservertutorial.net\/?page_id=1669"},"modified":"2020-04-11T20:12:28","modified_gmt":"2020-04-11T13:12:28","slug":"sql-server-select-into","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-basics\/sql-server-select-into\/","title":{"rendered":"SQL Server SELECT INTO"},"content":{"rendered":"\n

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

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

The SELECT INTO<\/code> statement creates a new table<\/a> and inserts rows<\/a> from the query into it.<\/p>\n\n\n\n

The following SELECT INTO<\/code> statement creates the destination<\/code> table and copies rows, which satisfy the WHERE<\/a><\/code> condition, from the source<\/code> table to the destination<\/code> table:<\/p>\n\n\n

SELECT<\/span> \n    select_list\nINTO<\/span> \n    destination\nFROM<\/span> \n    source<\/span>\n[WHERE<\/span> condition]\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
If you want to copy the partial data from the source<\/code> table, you use the WHERE<\/code> clause to specify which rows to copy. Similarly, you can specify which columns from the the source<\/code> table to copy to the destination<\/code> table by specifying them in the select list.<\/p>\n\n\n\n
SQL Server SELECT INTO examples #<\/a><\/h2>\n\n\n\n
Let’s take some examples of using the SELECT INTO<\/code> statement.<\/p>\n\n\n\n
A) Using SQL Server SELECT INTO to copy table within the same database example #<\/a><\/h3>\n\n\n\n
First, create a new schema for storing the new table.<\/p>\n\n\n
CREATE<\/span> SCHEMA<\/span> marketing;\nGO\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, create the marketing.customers<\/code> table like the sales.customers<\/code> table and copy all rows from the sales.customers<\/code> table to the marketing.customers<\/code> table:<\/p>\n\n\n
SELECT<\/span> \n    *\nINTO<\/span> \n    marketing.customers\nFROM<\/span> \n    sales.customers;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, query data from the the marketing.customers<\/code> table to verify the copy:<\/p>\n\n\n
SELECT<\/span> \n    *\nFROM<\/span> \n    marketing.customers;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following picture shows the partial output:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
B) Using SQL Server SELECT INTO statement to copy table across databases #<\/a><\/h3>\n\n\n\n
First, create a new database<\/a> named TestDb<\/code> for testing:<\/p>\n\n\n
CREATE<\/span> DATABASE<\/span> TestDb;\nGO\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, copy the sales.customers<\/code> from the current database (BikeStores<\/code>) to the TestDb.dbo.customers<\/code> table. This time, we just copy the customer identification, first name, last name, and email of customers who locate in California<\/code>:<\/p>\n\n\n
SELECT<\/span>    \n    customer_id, \n    first_name, \n    last_name, \n    email\nINTO<\/span> \n    TestDb.dbo.customers\nFROM<\/span>    \n    sales.customers\nWHERE<\/span> \n    state = 'CA'<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, query data from the TestDb.dbo.customers<\/code> to verify the copy:<\/p>\n\n\n
SELECT<\/span> \n    * \nFROM<\/span> \n    TestDb.dbo.customers;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the partial result set:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
In this tutorial, you have learned how to use the SQL Server SELECT INTO<\/code> statement to copy a table within the same database or across databases.<\/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
Note that SELECT INTO<\/code> statement does not copy constraints such as primary key<\/a> and indexes from the source<\/code> table to the destination<\/code> table.<\/p>\n\n\n\n