SQL Server/T-SQL/Transact SQL/Insert Data
Содержание
- 1 Checks for existing Product record: update or insert
- 2 Insert all statements in a batch or to roll back the entire statement group if an error occurs
- 3 Insert command and variables
- 4 Insert data to a table in the procedure
- 5 Managing Inserts Using Stored Procedures
- 6 Stored procedure: accepts a ID, name and city and inserts them as new row
Checks for existing Product record: update or insert
<source lang="sql">
12> 13> create table employee( 14> ID int, 15> name nvarchar (10), 16> salary int, 17> start_date datetime, 18> city nvarchar (10), 19> region char (1)) 20> GO 1> 2> insert into employee (ID, name, salary, start_date, city, region) 3> values (1, "Jason", 40420, "02/01/94", "New York", "W") 4> GO 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (2, "Robert",14420, "01/02/95", "Vancouver","N") 3> GO 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (3, "Celia", 24020, "12/03/96", "Toronto", "W") 3> GO 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (4, "Linda", 40620, "11/04/97", "New York", "N") 3> GO 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (5, "David", 80026, "10/05/98", "Vancouver","W") 3> GO 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (6, "James", 70060, "09/06/99", "Toronto", "N") 3> GO 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (7, "Alison",90620, "08/07/00", "New York", "W") 3> GO 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (8, "Chris", 26020, "07/08/01", "Vancouver","N") 3> GO 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (9, "Mary", 60020, "06/09/02", "Toronto", "W") 3> GO 1> 2> select * from employee 3> GO ID name salary start_date city region
---------- ----------- ----------------------- ---------- ------
1 Jason 40420 1994-02-01 00:00:00.000 New York W
2 Robert 14420 1995-01-02 00:00:00.000 Vancouver N
3 Celia 24020 1996-12-03 00:00:00.000 Toronto W
4 Linda 40620 1997-11-04 00:00:00.000 New York N
5 David 80026 1998-10-05 00:00:00.000 Vancouver W
6 James 70060 1999-09-06 00:00:00.000 Toronto N
7 Alison 90620 2000-08-07 00:00:00.000 New York W
8 Chris 26020 2001-07-08 00:00:00.000 Vancouver N
9 Mary 60020 2002-06-09 00:00:00.000 Toronto W
1> 2> -- Creating a Stored Procedure 3> 4> 5> /* Checks for existing Product record 6~ If exists, updates the record. If not, 7~ inserts new record 8~ */ 9> CREATE PROCEDURE spInsertOrUpdateEmployee 10> -- Input parameters -- 11> @Name nVarChar(50), 12> @City nVarChar(25), 13> @Salary Money 14> AS 15> IF EXISTS(SELECT * From Employee Where Name = @Name) 16> UPDATE Employee SET City = @City, Salary = @Salary 17> WHERE Name = @Name 18> ELSE 19> INSERT INTO Employee (Name, City, Salary) 20> SELECT @Name, @City, @Salary 21> GO 1> 2> EXEC spInsertOrUpdateEmployee @Name="a",@City="b",@Salary=999 3> GO 1> EXEC spInsertOrUpdateEmployee @Name="Mary",@City="b",@Salary=999 2> GO 1> 2> select * FROM employee 3> GO ID name salary start_date city region
---------- ----------- ----------------------- ---------- ------
1 Jason 40420 1994-02-01 00:00:00.000 New York W
2 Robert 14420 1995-01-02 00:00:00.000 Vancouver N
3 Celia 24020 1996-12-03 00:00:00.000 Toronto W
4 Linda 40620 1997-11-04 00:00:00.000 New York N
5 David 80026 1998-10-05 00:00:00.000 Vancouver W
6 James 70060 1999-09-06 00:00:00.000 Toronto N
7 Alison 90620 2000-08-07 00:00:00.000 New York W
8 Chris 26020 2001-07-08 00:00:00.000 Vancouver N
9 Mary 999 2002-06-09 00:00:00.000 b W
NULL a 999 NULL b NULL
1> 2> drop procedure spInsertOrUpdateEmployee 3> drop table employee 4> GO 1> 2>
</source>
Insert all statements in a batch or to roll back the entire statement group if an error occurs
<source lang="sql">
20> 21> 22> CREATE TABLE employee (emp_no INTEGER NOT NULL, 23> emp_fname CHAR(20) NOT NULL, 24> emp_lname CHAR(20) NOT NULL, 25> dept_no CHAR(4) NULL) 26> 27> insert into employee values(1, "Matthew", "Smith", "d3") 28> insert into employee values(2, "Ann", "Jones", "d3") 29> insert into employee values(3, "John", "Barrimore","d1") 30> insert into employee values(4, "James", "James", "d2") 31> insert into employee values(5, "Elsa", "Bertoni", "d2") 32> insert into employee values(6, "Elke", "Hansel", "d2") 33> insert into employee values(7, "Sybill", "Moser", "d1") 34> 35> select * from employee 36> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) emp_no emp_fname emp_lname dept_no
-------------------- -------------------- -------
1 Matthew Smith d3
2 Ann Jones d3
3 John Barrimore d1
4 James James d2
5 Elsa Bertoni d2
6 Elke Hansel d2
7 Sybill Moser d1
(7 rows affected) 1> 2> -- Use exception handling to insert all statements in a batch or to roll back the entire statement group if an error occurs. 3> 4> BEGIN TRY 5> BEGIN TRANSACTION 6> insert into employee values(11111, "Ann", "Smith","d2") 7> insert into employee values(22222, "Matthew", "Jones","d4") 8> insert into employee values(33333, "John", "Barrimore", "d2000000000000") 9> COMMIT TRANSACTION 10> PRINT "Transaction committed" 11> END TRY 12> BEGIN CATCH 13> ROLLBACK 14> PRINT "Transaction rolled back" 15> END CATCH 16> GO (1 rows affected) (1 rows affected) (1 rows affected) Transaction rolled back 1> select * from employee 2> GO emp_no emp_fname emp_lname dept_no
-------------------- -------------------- -------
1 Matthew Smith d3
2 Ann Jones d3
3 John Barrimore d1
4 James James d2
5 Elsa Bertoni d2
6 Elke Hansel d2
7 Sybill Moser d1
(7 rows affected) 1> drop table employee 2> GO 1>
</source>
Insert command and variables
<source lang="sql">
25> 26> IF EXISTS(SELECT name FROM sys.tables 27> WHERE name = "T") 28> DROP TABLE T 29> GO 1> 2> CREATE TABLE T ( 3> c1 int, 4> c2 varchar(8000) 5> ) 6> GO 1> 2> DECLARE @v1 varchar(max) 3> 4> SET @v1 = REPLICATE("A",7999) + "B" 5> INSERT T VALUES (1, @v1) 6> SELECT RIGHT(c2,2) "Right 2 of c2" FROM T 7> 8> SET @v1 = @v1 + "B" 9> INSERT T VALUES (2, @v1) 10> SELECT RIGHT(c2,2) "Right 2 of c2" FROM T 11> GO (1 rows affected) Right 2 of c2
AB Msg 8152, Level 16, State 10, Server sqle\SQLEXPRESS, Line 9 String or binary data would be truncated. The statement has been terminated. (1 rows affected) Right 2 of c2
AB (1 rows affected) 1> 2> drop table t 3> GO
</source>
Insert data to a table in the procedure
<source lang="sql">
1> create table employee( 2> ID int, 3> name nvarchar (10), 4> salary int, 5> start_date datetime, 6> city nvarchar (10), 7> region char (1)) 8> GO 1> 2> insert into employee (ID, name, salary, start_date, city, region) 3> values (1, "Jason", 40420, "02/01/94", "New York", "W") 4> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (2, "Robert",14420, "01/02/95", "Vancouver","N") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (3, "Celia", 24020, "12/03/96", "Toronto", "W") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (4, "Linda", 40620, "11/04/97", "New York", "N") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (5, "David", 80026, "10/05/98", "Vancouver","W") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (6, "James", 70060, "09/06/99", "Toronto", "N") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (7, "Alison",90620, "08/07/00", "New York", "W") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (8, "Chris", 26020, "07/08/01", "Vancouver","N") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (9, "Mary", 60020, "06/09/02", "Toronto", "W") 3> GO (1 rows affected) 1> 2> select * from employee 3> GO ID name salary start_date city region
---------- ----------- ----------------------- ---------- ------
1 Jason 40420 1994-02-01 00:00:00.000 New York W
2 Robert 14420 1995-01-02 00:00:00.000 Vancouver N
3 Celia 24020 1996-12-03 00:00:00.000 Toronto W
4 Linda 40620 1997-11-04 00:00:00.000 New York N
5 David 80026 1998-10-05 00:00:00.000 Vancouver W
6 James 70060 1999-09-06 00:00:00.000 Toronto N
7 Alison 90620 2000-08-07 00:00:00.000 New York W
8 Chris 26020 2001-07-08 00:00:00.000 Vancouver N
9 Mary 60020 2002-06-09 00:00:00.000 Toronto W
(9 rows affected) 1> 2> Drop procedure spInsertDate 3> GO 1> 2> CREATE PROC spInsertDate 3> @myDate datetime = NULL 4> AS 5> DECLARE @InsertedDate smalldatetime 6> IF DATEDIFF(dd,@myDate, GETDATE()) > 7 7> BEGIN 8> SELECT @InsertedDate = NULL 9> PRINT "Invalid Date" 10> PRINT "Supplied Date was greater than 7 days old." 11> PRINT "The value has been reset to NULL" 12> END 13> ELSE 14> BEGIN 15> SELECT @InsertedDate = CONVERT(datetime,(CONVERT(varchar,@myDate,112))) 16> PRINT "The Time of Day in Date was truncated" 17> END 18> INSERT INTO Employee (start_date) VALUES (@InsertedDate) 19> GO 1> 2> DECLARE @MyIdent int 3> 4> EXEC spInsertDate @myDate = "1/1/1999" 5> 6> SELECT * FROM Employee 7> GO Invalid Date Supplied Date was greater than 7 days old. The value has been reset to NULL (1 rows affected) ID name salary start_date city region
---------- ----------- ----------------------- ---------- ------
1 Jason 40420 1994-02-01 00:00:00.000 New York W
2 Robert 14420 1995-01-02 00:00:00.000 Vancouver N
3 Celia 24020 1996-12-03 00:00:00.000 Toronto W
4 Linda 40620 1997-11-04 00:00:00.000 New York N
5 David 80026 1998-10-05 00:00:00.000 Vancouver W
6 James 70060 1999-09-06 00:00:00.000 Toronto N
7 Alison 90620 2000-08-07 00:00:00.000 New York W
8 Chris 26020 2001-07-08 00:00:00.000 Vancouver N
9 Mary 60020 2002-06-09 00:00:00.000 Toronto W
NULL NULL NULL NULL NULL NULL
(10 rows affected) 1> 2> 3> drop table employee 4> GO 1>
</source>
Managing Inserts Using Stored Procedures
<source lang="sql">
1> create table employee( 2> ID int, 3> name nvarchar (10), 4> salary int, 5> start_date datetime, 6> city nvarchar (10), 7> region char (1)) 8> GO 1> 2> insert into employee (ID, name, salary, start_date, city, region) 3> values (1, "Jason", 40420, "02/01/94", "New York", "W") 4> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (2, "Robert",14420, "01/02/95", "Vancouver","N") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (3, "Celia", 24020, "12/03/96", "Toronto", "W") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (4, "Linda", 40620, "11/04/97", "New York", "N") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (5, "David", 80026, "10/05/98", "Vancouver","W") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (6, "James", 70060, "09/06/99", "Toronto", "N") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (7, "Alison",90620, "08/07/00", "New York", "W") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (8, "Chris", 26020, "07/08/01", "Vancouver","N") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (9, "Mary", 60020, "06/09/02", "Toronto", "W") 3> GO (1 rows affected) 1> 2> select * from employee 3> GO ID name salary start_date city region
---------- ----------- ----------------------- ---------- ------
1 Jason 40420 1994-02-01 00:00:00.000 New York W
2 Robert 14420 1995-01-02 00:00:00.000 Vancouver N
3 Celia 24020 1996-12-03 00:00:00.000 Toronto W
4 Linda 40620 1997-11-04 00:00:00.000 New York N
5 David 80026 1998-10-05 00:00:00.000 Vancouver W
6 James 70060 1999-09-06 00:00:00.000 Toronto N
7 Alison 90620 2000-08-07 00:00:00.000 New York W
8 Chris 26020 2001-07-08 00:00:00.000 Vancouver N
9 Mary 60020 2002-06-09 00:00:00.000 Toronto W
(9 rows affected) 1> 2> --Managing Inserts Using Stored Procedures 3> 4> CREATE PROCEDURE spIns_Employee 5> @Name nVarChar(50), 6> @City nVarChar(25), 7> @Salary Money 8> AS 9> INSERT INTO Employee (name, city, salary) 10> SELECT @Name, @City, @Salary 11> RETURN @@Identity 12> GO 1> 2> EXEC spIns_Employee "Name","City", 100 3> GO (1 rows affected) (1 rows affected) The "spIns_Employee" procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead. 1> 2> select * from employee 3> GO ID name salary start_date city region
---------- ----------- ----------------------- ---------- ------
1 Jason 40420 1994-02-01 00:00:00.000 New York W
2 Robert 14420 1995-01-02 00:00:00.000 Vancouver N
3 Celia 24020 1996-12-03 00:00:00.000 Toronto W
4 Linda 40620 1997-11-04 00:00:00.000 New York N
5 David 80026 1998-10-05 00:00:00.000 Vancouver W
6 James 70060 1999-09-06 00:00:00.000 Toronto N
7 Alison 90620 2000-08-07 00:00:00.000 New York W
8 Chris 26020 2001-07-08 00:00:00.000 Vancouver N
9 Mary 60020 2002-06-09 00:00:00.000 Toronto W
NULL Name 100 NULL City NULL
(10 rows affected) 1> 2> drop table employee 3> GO 1>
</source>
Stored procedure: accepts a ID, name and city and inserts them as new row
<source lang="sql">
1> create table employee( 2> ID int, 3> name nvarchar (10), 4> salary int, 5> start_date datetime, 6> city nvarchar (10), 7> region char (1)) 8> GO 1> 2> insert into employee (ID, name, salary, start_date, city, region) 3> values (1, "Jason", 40420, "02/01/94", "New York", "W") 4> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (2, "Robert",14420, "01/02/95", "Vancouver","N") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (3, "Celia", 24020, "12/03/96", "Toronto", "W") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (4, "Linda", 40620, "11/04/97", "New York", "N") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (5, "David", 80026, "10/05/98", "Vancouver","W") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (6, "James", 70060, "09/06/99", "Toronto", "N") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (7, "Alison",90620, "08/07/00", "New York", "W") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (8, "Chris", 26020, "07/08/01", "Vancouver","N") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (9, "Mary", 60020, "06/09/02", "Toronto", "W") 3> GO (1 rows affected) 1> 2> select * from employee 3> GO ID name salary start_date city region
---------- ----------- ----------------------- ---------- ------
1 Jason 40420 1994-02-01 00:00:00.000 New York W
2 Robert 14420 1995-01-02 00:00:00.000 Vancouver N
3 Celia 24020 1996-12-03 00:00:00.000 Toronto W
4 Linda 40620 1997-11-04 00:00:00.000 New York N
5 David 80026 1998-10-05 00:00:00.000 Vancouver W
6 James 70060 1999-09-06 00:00:00.000 Toronto N
7 Alison 90620 2000-08-07 00:00:00.000 New York W
8 Chris 26020 2001-07-08 00:00:00.000 Vancouver N
9 Mary 60020 2002-06-09 00:00:00.000 Toronto W
(9 rows affected) 1> 2> 3> --Stored procedure: accepts a ID, name and city and inserts them as new row 4> drop procedure spAddValue 5> GO 1> 2> CREATE PROC spAddValue @ID int 3> AS 4> INSERT INTO Employee (ID) VALUES (@ID) 5> GO 1> 2> EXEC spAddValue @ID = 99 3> GO (1 rows affected) 1> 2> select * from employee 3> GO ID name salary start_date city region
---------- ----------- ----------------------- ---------- ------
1 Jason 40420 1994-02-01 00:00:00.000 New York W
2 Robert 14420 1995-01-02 00:00:00.000 Vancouver N
3 Celia 24020 1996-12-03 00:00:00.000 Toronto W
4 Linda 40620 1997-11-04 00:00:00.000 New York N
5 David 80026 1998-10-05 00:00:00.000 Vancouver W
6 James 70060 1999-09-06 00:00:00.000 Toronto N
7 Alison 90620 2000-08-07 00:00:00.000 New York W
8 Chris 26020 2001-07-08 00:00:00.000 Vancouver N
9 Mary 60020 2002-06-09 00:00:00.000 Toronto W
99 NULL NULL NULL NULL NULL
(10 rows affected) 1> 2> drop table employee 3> GO 1>
</source>
