SQL Server/T-SQL Tutorial/Insert Delete Update/Insert into select
Содержание
Inserting Rows from Another Table
4>
5> CREATE TABLE Customers (
6> CustomerID nchar (5) NOT NULL ,
7> CompanyName nvarchar (40) NOT NULL ,
8> ContactName nvarchar (30) NULL ,
9> ContactTitle nvarchar (30) NULL ,
10> Address nvarchar (60) NULL ,
11> City nvarchar (15) NULL ,
12> Region nvarchar (15) NULL ,
13> PostalCode nvarchar (10) NULL ,
14> Country nvarchar (15) NULL ,
15> Phone nvarchar (24) NULL ,
16> Fax nvarchar (24) NULL
17> )
18> GO
1>
2> CREATE TABLE employee(
3> id INTEGER NOT NULL PRIMARY KEY,
4> first_name VARCHAR(10),
5> last_name VARCHAR(10),
6> salary DECIMAL(10,2),
7> start_Date DATETIME,
8> region VARCHAR(10),
9> city VARCHAR(20),
10> managerid INTEGER
11> );
12> 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>
5>
6> INSERT INTO Customers
7> (CompanyName)
8> SELECT First_Name
9> FROM Employee
10> GO
Msg 515, Level 16, State 2, Server J\SQLEXPRESS, Line 6
Cannot insert the value NULL into column "CustomerID", table "master.dbo.Customers"; column does not allow nulls. INSERT fails.
The statement has been terminated.
1>
2> select * from Customers;
3> GO
CustomerID CompanyName ContactName ContactTitle Address City Region
PostalCode Country Phone Fax
---------- ---------------------------------------- ------------------------------ ------------------------------ ------------------------------------------------------------ --------------- ---------
------ ---------- --------------- ------------------------ ------------------------
(0 rows affected)
1>
2> drop table Customers;
3> drop table employee;
4> GO
1>
INSERT. . . SELECT constants
5>
6>
7> CREATE TABLE Customers (
8> CustomerID nchar (5) NOT NULL ,
9> CompanyName nvarchar (40) NOT NULL ,
10> ContactName nvarchar (30) NULL ,
11> ContactTitle nvarchar (30) NULL ,
12> Address nvarchar (60) NULL ,
13> City nvarchar (15) NULL ,
14> Region nvarchar (15) NULL ,
15> PostalCode nvarchar (10) NULL ,
16> Country nvarchar (15) NULL ,
17> Phone nvarchar (24) NULL ,
18> Fax nvarchar (24) NULL
19> )
20> GO
1>
2>
3> INSERT INTO Customers (CustomerID, CompanyName) SELECT "1", "Bam Bam"
4> GO
(1 rows affected)
1>
2> select * from Customers;
3> GO
CustomerID CompanyName ContactName ContactTitle Address City Region
PostalCode Country Phone Fax
---------- ---------------------------------------- ------------------------------ ------------------------------ ------------------------------------------------------------ --------------- ---------
------ ---------- --------------- ------------------------ ------------------------
1 Bam Bam NULL NULL NULL NULL NULL
NULL NULL NULL NULL
(1 rows affected)
1>
2> drop table Customers;
3> GO
INSERT statement with a column list and select clause
3>
4>
5>
6> create table Billings (
7> BankerID INTEGER,
8> BillingNumber INTEGER,
9> BillingDate datetime,
10> BillingTotal INTEGER,
11> TermsID INTEGER,
12> BillingDueDate datetime ,
13> PaymentTotal INTEGER,
14> CreditTotal INTEGER
15>
16> );
17> 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>
3> create table BillingArchive (
4> BankerID INTEGER,
5> BillingNumber INTEGER,
6> BillingDate datetime,
7> BillingTotal INTEGER,
8> TermsID INTEGER,
9> BillingDueDate datetime ,
10> PaymentTotal INTEGER,
11> CreditTotal INTEGER
12>
13> );
14> GO
1>
2>
3> INSERT INTO BillingArchive
4> (BankerID, BillingNumber, BillingTotal, CreditTotal,
5> PaymentTotal, TermsID, BillingDate, BillingDueDate)
6> SELECT
7> BankerID, BillingNumber, BillingTotal, CreditTotal,
8> PaymentTotal, TermsID, BillingDate, BillingDueDate
9> FROM Billings
10> WHERE BillingTotal - PaymentTotal - CreditTotal = 0
11> GO
(0 rows affected)
1>
2>
3> drop table BillingArchive;
4> drop table Billings;
5> GO
The syntax of the INSERT statement for inserting rows selected from another table
INSERT [INTO] table_name [(column_list)]
SELECT column_list
FROM table_source
[WHERE search_condition]
Use insert...into...select in a stored procedure
7> CREATE TABLE employee(
8> id INTEGER NOT NULL PRIMARY KEY,
9> first_name VARCHAR(10),
10> last_name VARCHAR(10),
11> salary DECIMAL(10,2),
12> start_Date DATETIME,
13> region VARCHAR(10),
14> city VARCHAR(20),
15> managerid INTEGER
16> );
17> 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> CREATE PROCEDURE spIns_Employee
4> @FirstName nVarChar(50),
5> @LastName nVarChar(25),
6> @Salary Money
7> AS
8> INSERT INTO Employee (ID, First_name, last_Name, Salary)
9> SELECT 10, @FirstName, @LastName, @Salary
10> GO
1> spIns_Employee "Gadget", "Bond", 49.95
2> GO
(1 rows affected)
1> drop procedure spIns_Employee;
2> GO
1> drop table employee;
2> GO
1>
2>