Data Modification （INSERT、DELETE、UPDATE、MERGE）之INSERT
(基础知识，算是20年来第2次学习MSSQL吧，2005年折腾过一段时间的Oracle)
INSERT...VALUES...
INSERT...SELECT...
INSERT...EXEC...
SELECT...INTO...
BULK INSERT...
IDENTITY
下一篇当然会写DELETE、UPDATE、MERGE之类，觉得不熟悉的就慢点过，觉得熟悉的就快速过，
是不是觉得浪费时间啊，想想万丈高楼平地起嚒，o(*￣︶￣*)o
/*
Orders表在PIVOT、UNPIVOT中已经做了示例，下面表为OrdersA为区别
Microsoft SQL Server 2008 T-SQL Fundamentals_CN（Chapter 8 Inserting Data）
*/
if object_id('dbo.OrdersA','U') IS NOT NULL DROP TABLE dbo.OrdersA
CREATE TABLE dbo.OrdersA
(
orderid INT NOT NULL
CONSTRAINT PK_OrdersA PRIMARY KEY,
orderdate DATE NOT NULL
CONSTRAINT DFT_orderdate DEFAULT(CURRENT_TIMESTAMP),
empid INT NOT NULL,
custid VARCHAR(10) NOT NULL
)
GO
INSERT INTO dbo.OrdersA(orderid,orderdate,empid,custid)
values(10001,'20090212',3,'A')
INSERT INTO dbo.OrdersA(orderid,empid,custid) --如果没有指定某列，就用缺省值
values(10002,5,'B')
SELECT *
FROM OrdersA
-----------------------------------------------------------------------
--1. INSERT VALUES语句
--允许1条语句中指定由逗号分隔开的多行记录，作为 atomic operation处理(即一行失败，所有的都不处理)
INSERT INTO dbo.OrdersA(orderid,orderdate,empid,custid) --如果没有指定某列，就用缺省值
values(10003,'20090213',4,'B'),
(10004,'20090214',1,'A'),
(10005,'20090215',1,'C'),
(10006,'20090213',3,'C')
-----------------------------------------------------------------------
--虚拟表（Row Value Constructor or Table Value Constructor）
--是符合SQL标准的一种用法，下例对一个基于Values子句而定义的派生类查询）
SELECT *
FROM ( values(10003,'20090213',4,'B'),
(10004,'20090214',1,'A'),
(10005,'20090215',1,'C'),
(10006,'20090213',3,'C') )
AS o(orderid,orderdate,empid,custid)
-----------------------------------------------------------------------
SELECT *
FROM dbo.OrdersA
-----------------------------------------------------------------------
--2. INSERT...SELECT...语句，将一组由SELECT查询返回的结果行插入目标表中
SELECT *
FROM Northwind.dbo.Orders
INSERT INTO dbo.OrdersA(orderid,orderdate,empid,custid)
SELECT OrderID, OrderDate, EmployeeID, CustomerID
FROM Northwind.dbo.Orders
WHERE ShipCountry='UK'
GO
SELECT *
FROM dbo.OrdersA
-----------------------------------------------------------------------
--SQL 2008支持表值函数，所以不必进行下面这种方法了
--几乎所有的INSERT SELECT操作都进行完整模式的日志处理
--与以最小方式记录日志的操作相比，完整模式记录日志可能会相当慢（查： Operations That Can Be Minimally Logged）
INSERT INTO dbo.OrdersA(orderid,orderdate,empid,custid)
SELECT 10007,'20090215',2,'B' UNION ALL
SELECT 10008,'20090215',2,'C' UNION ALL
SELECT 10009,'20090216',2,'C' UNION ALL
SELECT 10010,'20090216',2,'A';
GO
SELECT *
FROM dbo.OrdersA
-----------------------------------------------------------------------
--3. INSERT EXEC语句（可以将存储过程或动态SQL批处理返回的结果集插入目标表）
IF OBJECT_ID('usp_getorders','P') IS NOT NULL
DROP PROC usp_getorders;
GO
--SELECT * FROM Northwind.dbo.Orders
CREATE PROC usp_getorders
@country AS NVARCHAR(40)
AS
BEGIN
SELECT OrderID,OrderDate,EmployeeID,CustomerID
FROM Northwind.dbo.Orders
WHERE shipcountry = @country
END
GO
EXEC usp_getorders @country = 'France';
INSERT INTO dbo.OrdersA(orderid,orderdate,empid,custid)
EXEC usp_getorders @country = 'France'
--发现将存储过程执行到Northwind库中去了，修改过来
--sp_helptext usp_getorders
--drop proc usp_getorders
SELECT *
FROM dbo.ordersA
-----------------------------------------------------------------------
-- 4. SELECT...INTO...语句（作用： 创建一个目标表，并用查询返回的结果来填充它）
--SELECT...INTO... 不是标准的SQL语句（它不是ANSI SQL标准的一部分）
--它不会从来源表中复制3样东西：constraints, indexes, and triggers
--只要不将名为“Recovery Model"的数据库属性设置成FULL，SELECT...INTO...就会按最小日志记录模式来执行操作
--如果想使用带有集合操作的SELECT...INTO...，应将INTO子句放在
IF OBJECT_ID('dbo.ordersA','U') IS NOT NULL DROP TABLE dbo.ordersA;
SELECT OrderID,orderdate,EmployeeID,CustomerID
INTO dbo.OrdersA
FROM Northwind.dbo.Orders
SELECT * FROM dbo.ordersA
--delete from dbo.ordersA
----------------------------------------
IF OBJECT_ID('dbo.ordersA','U') IS NOT NULL DROP TABLE dbo.ordersA;
SELECT OrderID,OrderDate, EmployeeID,CustomerID
INTO dbo.OrdersA --INTO放在集合的前面一个语句中
FROM Northwind.dbo.Orders
WHERE ShipCountry = 'UK'
EXCEPT --注意Except是差集（即在前面集合中、但不在后面集合中），此处重点不是讲解这个只是一个范例，说明INTO放在前面子句找那个
SELECT OrderID,OrderDate, EmployeeID,CustomerID
FROM Northwind.dbo.Orders
WHERE ShipCountry = 'France'
----------------------------------------
SELECT * FROM dbo.ordersA
-----------------------------------------------------------------------
--5. BULK INSERT语句（用于将文件中的数据导入一个已经存在的表）
--须指定目标表、源文件、一些选项（可以指定的选项很多，包括数据文件的类型、字段终止符、行终止符）
--将之前的试验数据直接使用bcp命令导出(在Dos命令提示符下执行)，同时删除掉之前的试验数据
--bcp testdb.dbo.Orders out c:\temp\OrdersA.txt -c -T
--delete from dbo.OrdersA
SELECT * FROM dbo.ordersA --查询OrdersA中已经没有数据
BULK INSERT dbo.OrdersA FROM 'c:\temp\OrdersA.txt'
WITH
(
DATAFILETYPE = 'char',
FIELDTERMINATOR = ' ',
ROWTERMINATOR = '\n'
);
SELECT * FROM dbo.ordersA
-----------------------------------------------------------------------
--6. IDENTITY属性
--注意2个函数： @@identity和SCOPE_IDENTITY()， 作用： 返回当前会话生成的最后一个标示值
--
IF OBJECT_ID('dbo.T1','U') IS NOT NULL DROP TABLE dbo.T1;
CREATE TABLE dbo.T1
(
keycol INT NOT NULL IDENTITY(1,1) --种子值、增量值均为1
CONSTRAINT PK_T1 PRIMARY KEY,
datacol VARCHAR(10) NOT NULL
CONSTRAINT CHK_T1_datacol CHECK(datacol LIKE '[A-Za-z]%') --以字母开头的字符串
)
INSERT INTO dbo.T1(datacol)
VALUES('AAAAA'),
('BBBBB'),
('CCCCC')
SELECT * FROM dbo.T1
SELECT IDENTITYCOL FROM dbo.T1 --SQL2008仍然保留 IDENTITYCOL，后续会去掉
SELECT $identity FROM dbo.T1 --此处不区分大小写
SELECT $IDENTITY FROM dbo.T1
SELECT $IDENTITY FROM dbo.T1
SELECT IDENT_CURRENT('dbo.T1')
DECLARE @new_key as INT
INSERT INTO dbo.T1(datacol) VALUES('AAAAA')
SET @new_key = SCOPE_IDENTITY()
SELECT @new_key as new_key
SELECT * FROM dbo.T1
--在之前INSERT会话窗口中(就是一个查询编辑器)查询时，[SCOPE_IDENTITY]、[@@identity]、[IDENT_CURRENT]相同
--如果新开一个会话窗口，[IDENT_CURRENT] 则为当前的 IDENTITY值
SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY],
@@identity AS [@@identity],
IDENT_CURRENT('dbo.T1') AS [IDENT_CURRENT]
SET IDENTITY_INSERT dbo.T1 ON --打开 IDENTITY_INSERT，可以补充那些被删除、或失败等不连续的IDENTITY值，虽然并没多大意义
INSERT INTO dbo.T1(keycol,datacol) VALUES(7,'FFFFF')
SET IDENTITY_INSERT dbo.T1 OFF
SET IDENTITY_INSERT dbo.T1 ON
INSERT INTO dbo.T1(keycol,datacol) VALUES(6,'EEEEE')
SET IDENTITY_INSERT dbo.T1 OFF
DBCC CHECKIDENT('dbo.T1',RESEED,0)
SELECT * FROM dbo.T1
--DELETE FROM dbo.T1
INSERT INTO dbo.T1(datacol)
VALUES('D2222')
--在 DBCC CHECKIDENT('dbo.T1',RESEED,0)之后，再插入一个没有打开 IDENTITY_INSERT参数的，会报错，因为第一个IDENTITY值已经存在了
--Msg 2627, Level 14, State 1, Line 203
--Violation of PRIMARY KEY constraint 'PK_T1'. Cannot insert duplicate key in object 'dbo.T1'. The duplicate key value is (3).
--The statement has been terminated.
SET IDENTITY_INSERT dbo.T1 ON
INSERT INTO dbo.T1(keycol,datacol) VALUES(15,'E33333')
SET IDENTITY_INSERT dbo.T1 OFF
SELECT * FROM dbo.T1
DBCC CHECKIDENT('dbo.T1',RESEED,0)
SELECT * FROM dbo.T1
SET IDENTITY_INSERT dbo.T1 ON
INSERT INTO dbo.T1(keycol,datacol) VALUES(10,'E33333')
SET IDENTITY_INSERT dbo.T1 OFF
INSERT INTO dbo.T1(datacol) VALUES('E33333')
--到此，本章学习基本完成（还需要进行练习，主要对BULK INSERT的应用，导入数据，等等）
-----------------------------------------------------------------------
--熟悉一下DBCC CHECKIDENT
create table T2
(
keyId int identity(1,1),
info1 varchar(10)
)
go
insert into T2 (info1)
values('a'),
('b'),
('c')
GO
SELECT * FROM T2
GO
DBCC CHECKIDENT('dbo.T2',RESEED,0)
insert into T2 (info1)
values('a1'),
('b1'),
('c1')
select * from T2
运行结果：
keyid info 1
1 a
2 b
3 c
1 a1
2 b1
3 c1
-----------------------------------------------------------------------
-----------------------------------------------------------------------
-----------------------------------------------------------------------