Summary
: in this tutorial, you will learn how to use the SQL Server
SELECT INTO
statement to copy a table.
Introduction to SQL Server
SELECT INTO
statement
The
SELECT INTO
statement
creates a new table
and
inserts rows
from the query into it.
The following
SELECT INTO
statement creates the
destination
table and copies rows, which satisfy the
WHERE
condition, from the
source
table to the
destination
table:
SELECT
select_list
destination
source
[WHERE condition]
Code language: SQL (Structured Query Language) (sql)
If you want to copy the partial data from the
source
table, you use the
WHERE
clause to specify which rows to copy. Similarly, you can specify which columns from the the
source
table to copy to the
destination
table by specifying them in the select list.
Note that
SELECT INTO
statement does not copy constraints such as
primary key
and indexes from the
source
table to the
destination
table.
SQL Server
SELECT INTO
examples
Let’s take some examples of using the
SELECT INTO
statement.
A) Using SQL Server
SELECT INTO
to copy table within the same database example
First, create a new schema for storing the new table.
CREATE SCHEMA marketing;
Code language: SQL (Structured Query Language) (sql)
Second, create the
marketing.customers
table like the
sales.customers
table and copy all rows from the
sales.customers
table to the
marketing.customers
table:
SELECT
marketing.customers
sales.customers;
Code language: SQL (Structured Query Language) (sql)
Third, query data from the the
marketing.customers
table to verify the copy:
SELECT
marketing.customers;
Code language: SQL (Structured Query Language) (sql)The following picture shows the partial output:
B) Using SQL Server
SELECT INTO
statement to copy table across databases
First,
create a new database
named
TestDb
for testing:
CREATE DATABASE TestDb;
Code language: SQL (Structured Query Language) (sql)
Second, copy the
sales.customers
from the current database (
BikeStores
) to the
TestDb.dbo.customers
table. This time, we just copy the customer identification, first name, last name, and email of customers who locate in
California
:
SELECT
customer_id,
first_name,
last_name,
email
TestDb.dbo.customers
sales.customers
WHERE