SQL Server/T-SQL Tutorial/Query/Select Into
Содержание
A statement that creates a complete copy of the Billings table
6>
7> create table Billings (
8> BankerID INTEGER,
9> BillingNumber INTEGER,
10> BillingDate datetime,
11> BillingTotal INTEGER,
12> TermsID INTEGER,
13> BillingDueDate datetime ,
14> PaymentTotal INTEGER,
15> CreditTotal INTEGER
16>
17> );
18> GO
1>
2> INSERT INTO Billings VALUES (1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321);
3> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321);
2> GO
(1 rows affected)
1> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321);
2> GO
(1 rows affected)
1>
2> create table BillingCopy (
3> BankerID INTEGER,
4> BillingNumber INTEGER,
5> BillingDate datetime,
6> BillingTotal INTEGER,
7> TermsID INTEGER,
8> BillingDueDate datetime ,
9> PaymentTotal INTEGER,
10> CreditTotal INTEGER
11>
12> );
13> GO
1>
2> SELECT *
3> INTO BillingCopy
4> FROM Billings
5> GO
1>
2> --A statement that creates a partial copy of the Billings table
3> SELECT *
4> INTO OldBillings
5> FROM Billings
6> WHERE BillingTotal - PaymentTotal - CreditTotal = 0
7> GO
1>
2> drop table BillingCopy;
3> drop table Billings;
4> GO
Creating a NULLable Column Using SELECT INTO
4>
5> CREATE TABLE employee(
6> id INTEGER NOT NULL PRIMARY KEY,
7> first_name VARCHAR(10),
8> last_name VARCHAR(10),
9> salary DECIMAL(10,2),
10> start_Date DATETIME,
11> region VARCHAR(10),
12> city VARCHAR(20),
13> managerid INTEGER
14> );
15> GO
1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10);
2> GO
(1 rows affected)
1>
2> select * from employee;
3> GO
id first_name last_name salary start_Date region city managerid
----------- ---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
(9 rows affected)
1>
2>
3> SELECT
4> ID,
5> CAST (NULL AS datetime) AS DateS
6> INTO
7> NewTable
8> FROM
9> Employee
10>
11> select * from NewTable
12> GO
(9 rows affected)
ID DateS
----------- -----------------------
1 NULL
2 NULL
3 NULL
4 NULL
5 NULL
6 NULL
7 NULL
8 NULL
9 NULL
(9 rows affected)
1>
2> drop table NewTable
3> GO
1>
2> drop table employee;
3> GO
The syntax of the SELECT INTO statement
SELECT select_list
INTO table_name
FROM table_source
[WHERE search_condition]
[GROUP BY group_by_list]
[HAVING search_condition]
[ORDER BY order_by_list]
Using INTO clause with where clause
4>
5> CREATE TABLE employee(
6> id INTEGER NOT NULL PRIMARY KEY,
7> first_name VARCHAR(10),
8> last_name VARCHAR(10),
9> salary DECIMAL(10,2),
10> start_Date DATETIME,
11> region VARCHAR(10),
12> city VARCHAR(20)
13> );
14> GO
1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver");
2> GO
(1 rows affected)
1>
2>
3> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown");
4> GO
(1 rows affected)
1>
2>
3> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris");
4> GO
(1 rows affected)
1>
2>
3> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London");
4> GO
(1 rows affected)
1>
2>
3> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton");
4> GO
(1 rows affected)
1>
2>
3> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary");
4> GO
(1 rows affected)
1>
2>
3> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York");
4> GO
(1 rows affected)
1>
2>
3> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina");
4> GO
(1 rows affected)
1>
2>
3> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto");
4> GO
(1 rows affected)
1>
2>
3>
4> select * from employee;
5> GO
id first_name last_name salary start_Date region city
----------- ---------- ---------- ------------ ----------------------- ---------- --------------------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto
(9 rows affected)
1>
2>
3>
4> SELECT ID,
5> first_Name
6> INTO Store_Archive
7> FROM employee
8> WHERE 1=0;
9>
10>
11> select * from Store_Archive;
12> GO
1>
2>
3>
4>
5> drop table employee;
6> GO
Using SELECT INTO to Create an Empty Table
15>
16> CREATE TABLE employee(
17> id INTEGER NOT NULL PRIMARY KEY,
18> first_name VARCHAR(10),
19> last_name VARCHAR(10),
20> salary DECIMAL(10,2),
21> start_Date DATETIME,
22> region VARCHAR(10),
23> city VARCHAR(20),
24> managerid INTEGER
25> );
26> GO
1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10);
2> GO
(1 rows affected)
1>
2> select * from employee;
3> GO
id first_name last_name salary start_Date region city managerid
----------- ---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
(9 rows affected)
1>
2>
3>
4> SELECT
5> ID
6> INTO
7> OrderDetails
8> FROM
9> Employee
10> WHERE
11> 1 = 0
12>
13> select * from OrderDetails
14> GO
(0 rows affected)
ID
-----------
(0 rows affected)
1>
2> drop table OrderDetails
3> GO
1> drop table employee;
2> GO
1>
Using the INTO Clause
The INTO clause of the SELECT statement creates a new table based on the columns and rows of the query results.
The syntax for INTO is as follows:
SELECT select_list
[INTO new_table_name]
FROM table_list
The INTO clause is followed by the new table name which must not already exist.
This can be a permanent, temporary, or global temporary table.
15> CREATE TABLE employee(
16> id INTEGER NOT NULL PRIMARY KEY,
17> first_name VARCHAR(10),
18> last_name VARCHAR(10),
19> salary DECIMAL(10,2),
20> start_Date DATETIME,
21> region VARCHAR(10),
22> city VARCHAR(20)
23> );
24> GO
1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver");
2> GO
(1 rows affected)
1>
2>
3> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown");
4> GO
(1 rows affected)
1>
2>
3> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris");
4> GO
(1 rows affected)
1>
2>
3> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London");
4> GO
(1 rows affected)
1>
2>
3> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton");
4> GO
(1 rows affected)
1>
2>
3> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary");
4> GO
(1 rows affected)
1>
2>
3> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York");
4> GO
(1 rows affected)
1>
2>
3> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina");
4> GO
(1 rows affected)
1>
2>
3> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto");
4> GO
(1 rows affected)
1>
2>
3>
4> select * from employee;
5> GO
id first_name last_name salary start_Date region city
----------- ---------- ---------- ------------ ----------------------- ---------- --------------------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto
(9 rows affected)
1>
2>
3> SELECT ID, first_Name
4> INTO Store_Archive
5> FROM employee;
6> GO
(9 rows affected)
1>
2>
3>
4>
5> drop table employee;
6> GO