T-SQL 之插入数据

DML

DML(Data Manipulation Language):主要用于对数据进行操作。DML主要包括INSERT、UPDATE、DELETE及MERGE语句。

我们平时项目中用得最多的SELECT语句其实也是属于DML语句。而本文重点介绍INSERT语句。

初衷

在以前的项目中，一般都是一条一条插入数据。最近有个项目，插入数据形式多变；有一条一条插入，也有批量插入；还有将一张表中的部分数据插入到另一张表中。

基于项目的以上特殊要求，故有了以下文字。

建表

为使示例简单，以大家耳熟能详的tb_User为例。代码如下：

USE tempdb;
IF OBJECT_ID('dbo.tb_User','U') IS NOT NULL DROP TABLE dbo.tb_User;
CREATE TABLE dbo.tb_User
(
    UserId INT NOT NULL IDENTITY(1,1) CONSTRAINT PK_User PRIMARY KEY,
    UserName NVARCHAR(50) NOT NULL,
    Password NVARCHAR(50) NOT NULL
)

插入语句

T-SQL提供了以下几种将数据插入表中的语句

1. INSERT VALUES
2. INSERT SELECT
3. INSERT EXEC
4. SELECT INTO
5. BULK INSERT

以下将分别介绍

INSERT VALUES

语法：

INSERT INTO 表名称 VALUES (值1, 值2,....) 

亦可指定要插入的列

INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)

示例：

insert into tb_User values('张三','123456');
insert into tb_User(UserName,Password) values('李四','234567');

 

如果为某个列指定了值，Sql Server将使用这个值。如果没有为某个列指定值，SQL Server将检查是否为该列定义了默认值。

如果定义了默认值，则使用默认值。没有没有定义默认值，则检查该列是否允许为NULL值，若允许，为该列使用NULL值。

如果在INSERT 语句中既未某列指定一个值，也没有什么机制能让他自动获得一个值。那么这个INSERT 语句就会执行失败。

请看：

insert into tb_User(UserName) values('999999');

此处未为password列指定初值，且password 值限制为Not NUll。执行语句后，提示：

消息 515，级别 16，状态 2，第 1 行 
不能将值 NULL 插入列 'Password'，表 'tempdb.dbo.tb_User'；列不允许有 Null 值。INSERT 失败。 
语句已终止。 

如果修改password列，使其允许为空。

ALTER TABLE tb_User
ALTER COLUMN password NVARCHAR(50) NULL; 

插入

insert into tb_User(UserName) values('999999')

提示：

(1 行受影响)

增强VALUES语句

SQL Server2008增强了VALUES语句的功能，允许一条语句中指定由逗号分隔开的多行记录。

下面的语句向tb_User表中插入4行语句

INSERT INTO tb_User(UserName,Password)
VALUES
    ('Lucy','123456'),
    ('Lily','234567'),
    ('张三','9999'),
    ('李四','6666');

 

以上的语句是原子操作的，如果其中一行在插入时失败，那么整个插入语句将失败。

再看插入如下语句，其中values子句共1003行。

INSERT INTO tb_User(UserName,Password)
VALUES
    ('Lucy','123456'),
    ('Lily','234567'),
    .
    .
    .
    ('张三','9999'),
    ('李四','6666');

提示：

消息 10738，级别 15，状态 1，第 1003 行 
INSERT 语句中行值表达式的数目超出了 1000 行值的最大允许值。 

所以：使用单个 INSERT 语句可插入的最大行数为 1000。

INSERT SELECT语句

语法：

INSERT INTO 表名称1（值1,值2…）

SELECT 值1,值2… FROM 表名称2 WHERE …

insert select 语句将一组有select查询返回的结果行插入目标表中。

他的语法与insert values非常相似，只不过这里用select查询代替了select语句。

示例：

首先新建一tb_Person表。为了测试，此处tb_Person表与tb_User表中的列部分相同

CREATE TABLE tb_Person
(
    PersonId INT NOT NULL IDENTITY(1,1) CONSTRAINT PK_Person PRIMARY KEY,
    Name NVARCHAR(50) NULL,
    Age INT NULL,
    Address NVARCHAR(50) NULL
)

执行以下语句

INSERT INTO tb_Person(Name)
SELECT UserName FROM tb_User;

提示

(4 行受影响)

查询tb_Person表，结果如下：

 

源表与目标表中列可以不相同，但insert select语句中的列必须相同INSERT SELECT语句也是原子操作的，所以如果任何一行插入目标表失败，那么结果将是所有的行都不会插入表

如下语句：

INSERT INTO tb_Person
SELECT UserName FROM tb_User;

提示

消息 213，级别 16，状态 1，第 1 行 
列名或所提供值的数目与表定义不匹配。 

解决方法：tb_User表与tb_Person表列定义相同

 

再如下例：

INSERT INTO tb_Person(Name,Address)
SELECT UserName FROM tb_User;

提示

消息 120，级别 15，状态 1，第 1 行 
INSERT 语句的选择列表包含的项少于插入列表中的项。SELECT 语句中值的数目必须与 INSERT 语句中列的数目匹配。

数据库如果没有目标表。下例中数据库无tb_UserTest表。执行如下语句：

INSERT INTO tb_UserTest
SELECT UserName FROM tb_User;

提示：

消息 208，级别 16，状态 1，第 1 行 
对象名 'tb_UserTest' 无效。 

解决方法：使用SELECT INTO 语句。后面将简单介绍。

 

如果想在SQL Server 2008之前的语句中构建一个常量的虚拟表，就不得不使用多个SELECT语句，由每个SELECT语句返回一个基于常量值的行，

再用UNION ALL集合运算把各行合并一起。

在INSERT SELECT 语句的应用中，用这种技术也可以在一条语句中一次性插入多个基于常量的行，而且操作也是原子操作

示例：

INSERT INTO tb_User(UserName,Password)
SELECT 'LUCY','88888' UNION ALL
SELECT 'LILY','66666' UNION ALL
SELECT 'JIM','55555';

 

以下三个insert语句在项目中无使用，只做简单介绍。若可能，以后再进一步拓展。

INSERT EXEC语句

INSERT EXEC语句可以把存储过程或者动态SQL批处理的结果集插入目标表。在语法上，INSERT EXEC语句与INSERT SELECT语法非常相似。

只不过这里使用的不是SELECT语句，而是EXEC语句。

以下代码在tempdb数据库中创建一个名为sp_getUser的存储过程，返回指定UserName的用户

USE tempdb;
IF OBJECT_ID('sp_getUser','P') IS NOT NULL
DROP PROC sp_getUser;
GO
CREATE PROC sp_getUser
 @UserName AS NVARCHAR(50)
AS
SELECT UserName FROM tb_User
WHERE UserName = @UserName;
GO

为了测试这个存储过程，用输入‘LUCY’来执行它。

exec sp_getUser @UserName='LUCY';

得到如下结果：

使用INSERT EXEC 语句可以把该存储过程返回的结果集插入tempdb数据库中的tb_Person表

INSERT INTO tb_Person(Name) EXEC sp_getUser @UserName='LUCY';

输出结果：

(3 行受影响)

SELECT INTO语句

SELECT INTO语句的作用是创建一个目标表，并用查询返回的结果来填充它。

SELECT INTO语句并非ANSI SQL语句的一部分。不能用这个语句向已经存在的表中插入数据。

如：本人本机tempdb数据库中已有tb_UserTest表。执行如下语句：

select UserName,Password  into tb_UserTest from tb_User;

提示：

消息 2714，级别 16，状态 6，第 1 行 
数据库中已存在名为 'tb_UserTest' 的对象。 

现在我删除tempdb数据库中的tb_UserTest表，继续执行上述语句。

执行结果如下：

(10 行受影响) 

 

目标表的结构和数据依赖于来源表。SELECT INTO语句会复制来源表的数据和基本结构，包括列名、数据类型、是否允许为NULL及IDENTITY属性。

不过，SELECT INTO语句不会从来源表复制3样东西：约束、索引和触发器。如果目标表中需要这些东西，需亲自创建他们。

 

BULK INSERT语句

BULK INSERT 语句用于将文件中的数据导入一个已经存在的表。在这个语句中，须要指定的项很多。

包括数据文件的类型、字段终止符和行终止符。

例如：以下代码将文件"C:\temp\orders.txt"中的数据大容量插入tempdb的Orders表。

同时指定数据文件类型为字符格式，字段终止符为逗号，行终止符为换行符。

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