T-SQL 数据插入操作解析

T-SQL提供了几种用于将数据插入表中的语句： INSERT VALUES、INSERT SELECT、 INSERT EXEC、SELECT INTO及BULKINSERT。

 

一.INSERT VALUES语句

INSERT VALUES语句可以将基于指定值的行插入表。

INSERT INTO dbo.orders(orderid,orderdate,empid,custid) VALUES(l0001,'20090212',3,'A') ;

表名之后可以指定目标列的名称，虽然显式地指定列名是可选的，但这样做可以对语句中指定的列值和列名之间的关联关系进行控制，而不必单纯地依赖在定义表时（或者在对表结构进行最后一次修改以后）各个列的出现顺序。

SQL Server 2008增强了VALUES语句的功能，允许在一条语句中指定由逗号分隔开的多行记录。例如，下面的语句向Orders表中插入4行数据：

INSERT INTO dbo.orders(orderid,orderdate,empid,custid)
VALUES(l0001,'20090212',3,'A'),
VALUES(l0002,'20100212',3,'B'),
VALUES(l0003,'20110212',3,'C'),
VALUES(l0004,'20120212',3,'D'),

上面这个语句是作为原子操作 (atomic operation) 而处理的， 这意味着如果有任何一行在插入表时失败， 那么语句中的所有行都不会插入表。

SQL Server 2008 对 VALUES 语句的增强还不只限于这些。它不仅增强了 INSERT VALUES 语句， 而且还增强了 VALUES 子句本身的功能， 现在可以用它来构建虚拟表 (virtual table入这种功能称为行值构造函数(Row Value Constructor)或表值构造函数(Table Value Constructor) , 是符合 SQL 标准的一种用法。这意味着在 SQL Server 2008 中可以定义一个基千 VALUES 子句的表表达式。 下面这个例子就是对一个基于VALUES 子句而定义的派生表进行查询：

SELECT * 
FROM(VALUES
    (l0001,'20090212',3,'A'),
    (l0002,'20100212',3,'B'),
    (l0003,'20110212',3,'C'),
    (l0004,'20120212',3,'D'))
AS O(orderid, orderdate, empid, custid); 

紧跟在包含表值构造函数的一对圆括号之后， 可以为虚拟表指定一个别名。

 

二.INSERT SELECT语句

INSERT SELECT 语句可以将一组由 SELECT 查询返回的结果行插入目标表中。 它的语法和INSERT VALUES 语句非常相似， 只不过这里是用SELECT 查询代替了 VALUES 子句。

INSERT INTO    dbo.Orders(orderid,orderdate,empid,custid)
    SELECT orderid, orderdate, empid, custid 
    FROM Sales.Orders 
    WHERE shipcountry = 'UK'; 

 

三.INSERT EXEC语句

INSERT EXEC语句可以把存储过程或动态SQL批处理返回的结果集插入目标表。INSERT EXEC语句和INSERT SELECT语句非常相似，只不过这里使用的不是 SELECT语句，而是要指定一个EXEC语句。

INSERT INTO dbo.Orders(orderid, orderdate, empid, custid) 
  EXEC Sales.usp_getorders @country='France';

 

四.SELECT INTO语句

SELECT INTO语句的作用是创建一个目标表， 并用查询返回的结果来填充它。不能用这个语句向已经存在的表中插入数据。 按照语法， 只要简单地将 INTO<目标表名称＞加到用于生成结果集的 SELECT查询的FROM子句前面就可以了。 例如， 以下代码在 tempdb数据库中创建一个名为dbo.Orders的表。

SELECT orderid, orderdate, empid, custid 
INTO tmpdb.dbo.Orders 
FROM Sales.orders; 

目标表的结构和数据依赖于来源表。 SELECT INTO 语句会复制来源表的基本结构（包括列名、 数据类型、 是否允许为 NULL 及 IDENTITY 属性）和数据。 不过， SELECT INTO 语句不会从来源表中复制3样东西：约束、索引及触发器。 如果目标表中需要这些东西， 则需要亲自创建它们。

如果想使用带有集合操作的 SELECT INTO 语句， 应该把 INTO子句放在第一个查询的 FROM 子句之前。 例如， 下面的 SELECT INTO 语句创建一个名为 Locations 的表， 并用一个 EXCEPT 集合操作的结果来填充这个新表， 该集合操作返回的是客户地址， 但不是雇员地址的那些行。

SELECT country, region, city 
INTO dbo.Locations 
FROM Sales.customers 
EXCEPT 
SELECT country, region, city 
FROM HR.Employees; 

 

五.BULK INSERT语句

BULK INSERT 语句用于将文件中的数据导入一个已经存在的表。 在这个语句中，须要指定目标表、源文件及一些选项。可以指定的选项很多，包括数据文件的类型、字段终止符和行终止符等。例如， 以下代码将文件 "c:\temp\orders.txt" 中的数据大容量插入 (bulk insert) tempdb 的 dbo.Orders 表， 同时指定数据文件类型为字符格式， 字段终止符为逗号， 行终止符为换行符 (\t)。

BULK INSERT dbo.Orders FROM 'c:\temp\orders.txt' 
WITH
(
    DATAFILETYPE = 'char',
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n'
);

 

六.IDENTITY属性

在SQL Server中可以为列定义 —个名为IDENTITY的属性（标识列 ），它是一个小数位数为0 (或没有分数）的任意数字 。当把值插入(INSERT)有标识列的表 时， 数据库引擎会根据列定义中提供的一个种子(seed, 第 1 个值）和增量（步长值）自动生成递增的标识值。 通常是用这 种属性生成代理键(surrogate key) , 这是一 种由系统生成的键，而不是由应用数据派生出来的键 。在tempdb 数据库中创建一个名为dbo.T1的表：

USE tempdb; 
IF OBJECT_ID('dbo.T1','U') IS NOT NULL DROP TABLE dbo.T1; 
CREATE TABLE dbo.T1
(
    keycol INT NOT NULL IDENTITY(1,1) CONSTRAINT PK_Tl PRIMARY KEY, 
    datacol VARCHAR(10) NOT NULL CONSTRAINT CHK_T1_datacol CHECK(datacol LIKE '[A-Za-z]%') 
) 

在INSERT 语句中，应该完全忽略标识列， 就像它们在表中不存在一样。 例如，以下代码向该表 插入3行数据时， 只为datacol列指定值：

INSERT INTO dbo.T1(datacol) VALUES('AAAAA'); 
INSERT INTO dbo.T1(datacol) VALUES('CCCCC'); 
INSERT INTO dbo.T1(datacol) VALUES('BBBBB');

当查询这个表时，自然可以通过标识列的列名来引用它（在这个例子中是keycol)。SQL Server为引用标识列提供了一种更通用的格式：$identity。

当在表中新插入一行时，SQLServer会根据表中当前的标识值和增量生成一个新的标识值。如果需要获得这个新生产的标识值，可以查询SCOPE_IDENTJTY()。COPE_IDENTJTY()返回当前作用域（例如，同一存储过程）内会话生成的最后一个标识值。

DECLARE @new_key AS INT;
INSERT INTO dbo.T1(datacol) VALUES('AACCC');
SET @new_key = SCOPE_IDENTITY()
SELECT @new_key AS new_key 

 

 

好了，本篇文章就介绍到这儿，欢迎大家留言交流；喜欢或有帮助到您的话，点个赞或推荐支持一下！

 

转载于:https://www.cnblogs.com/johnvwan/p/9497700.html