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
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>
Insert all statements in a batch or to roll back the entire statement group if an error occurs
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>
Insert command and variables
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
Insert data to a table in the procedure
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>
Managing Inserts Using Stored Procedures
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>
Stored procedure: accepts a ID, name and city and inserts them as new row
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>