SQL Server/T-SQL/Insert Delete Update/Insert
Содержание
Inserting Several Records in a Query Batch
<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> --Inserting Several Records in a Query Batch 3> 4> SET QUOTED_IDENTIFIER OFF 5> GO 1> INSERT INTO Employee (ID, Name) VALUES(999,"Jason A") 2> INSERT INTO Employee (ID, Name) VALUES(888,"Martin A") 3> INSERT INTO Employee (ID, Name) VALUES(777,"Dave S") 4> INSERT INTO Employee (ID, Name) VALUES(666,"Colin J") 5> GO (1 rows affected) (1 rows affected) (1 rows affected) (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
999 Jason A NULL NULL NULL NULL
888 Martin A NULL NULL NULL NULL
777 Dave S NULL NULL NULL NULL
666 Colin J NULL NULL NULL NULL
(13 rows affected) 1> 2> drop table employee 3> GO 1>
</source>
Inserts one or more rows selected with a subquery
<source lang="sql">
27> 28> 29> CREATE TABLE department(dept_no CHAR(4) NOT NULL, 30> dept_name CHAR(25) NOT NULL, 31> location CHAR(30) NULL) 32> 33> insert into department values ("d1", "developer", "Dallas") 34> insert into department values ("d2", "tester", "Seattle") 35> insert into department values ("d3", "marketing", "Dallas") 36> 37> select * from department 38> GO (1 rows affected) (1 rows affected) (1 rows affected) dept_no dept_name location
------------------------- ------------------------------
d1 developer Dallas d2 tester Seattle d3 marketing Dallas (3 rows affected) 1> 2> -- Inserts one or more rows selected with a subquery. 3> 4> CREATE TABLE dallas_dept 5> (dept_no CHAR(4) NOT NULL, 6> dept_name CHAR(20) NOT NULL) 7> 8> INSERT INTO dallas_dept (dept_no, dept_name) 9> SELECT dept_no, dept_name FROM department WHERE location = "Dallas" 10> 11> select * from dallas_dept 12> Go (2 rows affected) dept_no dept_name
--------------------
d1 developer d3 marketing (2 rows affected) 1> 2> drop table department 3> drop table dallas_dept 4> GO 1> 2>
</source>
Insert with default value
<source lang="sql">
2> CREATE TABLE publishers2 3> ( 4> pub_id int NOT NULL PRIMARY KEY IDENTITY, 5> pub_name varchar(40) NULL DEFAULT ("Anonymous"), 6> city varchar(20) NULL, 7> state char(2) NULL, 8> country varchar(30) NOT NULL DEFAULT("USA") 9> ) 10> GO 1> 2> 3> INSERT publishers2 VALUES ("AAA Publishing", "Vancouver", "BC", 4> "Canada") 5> 6> INSERT INTO publishers2 VALUES ("Best Publishing", "Mexico City", 7> NULL, "Mexico") 8> 9> INSERT INTO publishers2 (pub_name, city, state, country) 10> VALUES ("Complete Publishing", "Washington", "DC", "United States") 11> 12> INSERT publishers2 (state, city) VALUES ("WA", "Poulsbo") 13> 14> INSERT publishers2 VALUES (NULL, NULL, NULL, DEFAULT) 15> 16> INSERT publishers2 VALUES (DEFAULT, NULL, "WA", DEFAULT) 17> 18> INSERT publishers2 VALUES (NULL, DEFAULT, DEFAULT, DEFAULT) 19> 20> INSERT publishers2 DEFAULT VALUES 21> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> drop table publishers2; 3> GO 1>
</source>
Leave column as NULL
<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> -- Leave column as NULL 4> INSERT employee (Name) VALUES ("Bedford") 5> GO (1 rows affected) 1> 2> 3> select * from employee 4> 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 Bedford NULL NULL NULL NULL
(10 rows affected) 1> 2> drop table employee 3> GO 1>
</source>
The order of the columns in the insert statement
<source lang="sql">
4> 5> 6> CREATE TABLE employee (emp_no INTEGER NOT NULL, 7> emp_fname CHAR(20) NOT NULL, 8> emp_lname CHAR(20) NOT NULL, 9> dept_no CHAR(4) NULL) 10> 11> insert into employee values(1, "Matthew", "Smith", "d3") 12> insert into employee values(2, "Ann", "Jones", "d3") 13> insert into employee values(3, "John", "Barrimore","d1") 14> insert into employee values(4, "James", "James", "d2") 15> insert into employee values(5, "Elsa", "Bertoni", "d2") 16> insert into employee values(6, "Elke", "Hansel", "d2") 17> insert into employee values(7, "Sybill", "Moser", "d1") 18> 19> select * from employee 20> 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> 3> -- The order of column names in the VALUE clause can be different from the order of those columns determined in the CREATE TABLE statement 4> 5> INSERT INTO employee (emp_lname, emp_fname, dept_no, emp_no) 6> VALUES ("Davis", "Dave", "d1", 15201) 7> 8> select * from employee 9> GO (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
15201 Dave Davis d1
(8 rows affected) 1> 2> drop table employee 3> GO 1>
</source>
