6.1 SQL Server插入数据

SQL Server INSERT

目录

• SQL Server INSERT
  • INSERT语句简介
  • SQL Server插入语句示例
    • INSERT完整写法
    • INSERT简写
  • 插入同时返回插入的值
  • 在标识(IDENTITY)列中插入显式值
  • 插入多行数据
    • SQL Server插入多行示例
  • INSERT INTO SELECT语句
    • INSERT INTO SELECT简介
    • INSERT INTO SELECT示例
    • 插入另一个表中的所有行
    • 插入另一个表中的部分行
    • 插入前N行
    • 插入行的顶部百分比

INSERT语句简介

要向表中添加一行或多行，可以使用INSERT语句。下面说明了INSERT语句的最基本形式：

INSERT INTO table_name (column_list)
VALUES (value_list);
--简写
INSERT INTO table_name
VALUES (value_list);

让我们来看一下这个语法的更多细节。

首先，可以指定一个表名，通常，可以通过架构(Scheme)来引用表明，比如dbo.products，启动dbo就是架构名，products就是表名。

其次，指定要在其中插入数据的一列或多列列表。必须将列列表括在括号中，并用逗号分隔列。

如果表中的某列未出现在列列表中，则SQL Server必须能够提供插入值，否则无法插入该行，比如：自增主键就不用手动插入值，SQL Server会自动提供插入值。

SQL Server会自动为表中可用但未出现在INSERT语句列列表中的列使用以下值：

• 列为IDENTITY属性的标识列，下一个值自动自增。
• 如果列具有指定的默认值，则为默认值。
• 如果列的数据类型是时间戳数据类型，则为当前时间戳值。
• 如果列可为空，则为NULL。
• 如果列是计算列，则为计算值。

第三，提供要插入VALUES子句的值列表。列列表中的每列必须在值列表中具有相应的值。此外，必须将值列表括在括号中。

SQL Server插入语句示例

先创建一个名为Students的学生表

create table dbo.Students
(
    Id int primary key identity(1,1), -- 每添加一条数据，Id从1开始，每次自增1
    NickName nvarchar(15), -- unicode
    StudentNo char(11) , -- 学号
    Sex nchar(2),
    Account varchar(20), -- 账号
    [Password] varchar(50) -- 密码
);

表中的主键Id列是一个标识列，所以该列的值会在添加数据时自动自增。

向表中添加几行数据：

INSERT完整写法

insert into Students(
    NickName,StudentNo,Sex,Account,[Password]
)
 values
('张三','2001','男','user1','123456')

上面的SQL语句，我们指定了除了Id列外的其他五列，没有指定Id列，因为SQL Server会自动给Id列提供自增值。

正常情况，以上语句会执行成功:

可以执行一下语句验证：

select * from dbo.Students

返回：

INSERT简写

insert into Students values
('张三','2001','男','user1','123456')

这种方式不需要指定字段名称，但是需要指定所有字段的值（除了自增的主键不需要）,并且默认值也需要手动加上，不可省略。

使用简写的方式，如果我不想给所有字段都赋值，怎么办？（把不想赋值的字段赋null(前提是这个字段可以允许为空)：

insert into UserInfo values('高大侠','7','保密',null,null)

插入同时返回插入的值

要拿到插入的值，可以使用OUTPUT子句。例如，以下语句将新行插入Students表，并返回主键Id列的插入值：

insert into Students(
    NickName,StudentNo,Sex,Account,[Password]
) OUTPUT inserted.Id
 values
('李四','2002','男','user2','123456')

执行结果：

如果要获取多个插入的列的值，如下所示：

insert into Students(
    NickName,StudentNo,Sex,Account,[Password]
) OUTPUT 
    inserted.Id,
    inserted.NickName,
    inserted.StudentNo,
    inserted.Sex,
    inserted.Account,
    inserted.[Password]
 values
    ('王五','2003','女','user3','123456')

执行结果：

在标识(IDENTITY)列中插入显式值

通常，您是不需要指定表示列的值，因为SQL Server会自动提供标识列的值。

但是，在某些情况下，您可能希望在标识列中插入一个值，例如数据迁移。

看如下插入语句：

insert into Students(
    Id,NickName,StudentNo,Sex,Account,[Password]
)
 values
(4,'王麻子','2004','男','user4','123456')

执行，SQL Server会报如下错误：

Cannot insert explicit value for identity column in table 'Students' when IDENTITY_INSERT is set to OFF.

翻译：当IDENTITY_INSERT设置为OFF时，无法在表Students中插入identity列(Id列)的显式值。

要为标识列插入显式值，必须首先执行以下语句：

SET IDENTITY_INSERT table_name ON;

要关闭标识插入，请使用类似的语句：

SET IDENTITY_INSERT table_name OFF;

让我们执行以下语句，在学生表中插入主键Id列的值：

SET IDENTITY_INSERT dbo.Students ON;

insert into dbo.Students(
    Id,NickName,StudentNo,Sex,Account,[Password]
)
 values
(4,'王麻子','2004','男','user4','123456')

SET IDENTITY_INSERT dbo.Students OFF;

在本例中，我们首先打开了标识插入，然后为标识列插入了一个具有显式值的行，最后关闭了标识插入。
执行成功

插入后的数据：

插入多行数据

语法如下：

INSERT INTO table_name (column_list)
VALUES
    (value_list_1),
    (value_list_2),
    ...
    (value_list_n);
--简写
INSERT INTO table_name
VALUES
    (value_list_1),
    (value_list_2),
    ...
    (value_list_n);

在这种语法中，不是使用单个值列表，而是使用多个逗号分隔的值列表进行插入

使用这种形式的INSERT语句，一次可以插入的行数为1000行。如果要插入更多的行，应考虑使用多个INSERT语句、BULK INSERT或派生表。

请注意，此插入多行语法仅在SQL Server 2008或更高版本中受支持。

要插入SELECT语句返回的多行，可以使用INSERT INTO SELECT子句。

SQL Server插入多行示例

insert into Students(
    NickName,StudentNo,Sex,Account,[Password]
)
 values
('张三','2001','男','user1','123456'),
('李四','2002','男','user2','123456'),
('王麻子','2003','女','user3','123456')

简写如下：

insert into Students
values
('张三','2001','男','user1','123456'),
('李四','2002','男','user2','123456'),
('王麻子','2003','女','user3','123456')

INSERT INTO SELECT语句

INSERT INTO SELECT简介

要将其他表中的数据插入表中，请使用以下SQL Server INSERT INTO SELECT语句：

INSERT  [ TOP ( expression ) [ PERCENT ] ] 
INTO target_table (column_list)
query

在这种语法中，语句将query返回的行插入到target_table中。3

query是从其他表检索数据的任何有效SELECT语句。它必须返回与column_list中指定的列相对应的值。

TOP子句部分是可选的。它允许您指定要插入目标表的查询返回的行数。如果使用percent选项，则语句将插入行的百分比。请注意，最佳做法是始终将TOP子句与ORDER BY子句一起使用。

INSERT INTO SELECT示例

首先创建一个名为addresses的表：

CREATE TABLE sales.addresses (
    address_id INT IDENTITY PRIMARY KEY,
    street VARCHAR (255) NOT NULL,
    city VARCHAR (50),
    state VARCHAR (25),
    zip_code VARCHAR (5)
); 

插入另一个表中的所有行

以下语句将customers（客户）表中的所有地址插入addresses（地址）表：

INSERT INTO sales.addresses (street, city, state, zip_code) 
SELECT
    street,
    city,
    state,
    zip_code
FROM
    sales.customers
ORDER BY
    first_name,
    last_name; 

要验证插入，请使用以下查询：

SELECT
    *
FROM
    sales.addresses;

输出：

插入另一个表中的部分行

以下语句将位于Santa Cruz和Baldwin的商店地址添加到addresses(地址)表中：

INSERT INTO 
    sales.addresses (street, city, state, zip_code) 
SELECT
    street,
    city,
    state,
    zip_code
FROM
    sales.stores
WHERE
    city IN ('Santa Cruz', 'Baldwin')

插入前N行

要插入按名字和姓氏排序的前10位客户，请使用INSERT TOP INTO SELECT语句，如下所示：

INSERT TOP (10) 
INTO sales.addresses (street, city, state, zip_code) 
SELECT
    street,
    city,
    state,
    zip_code
FROM
    sales.customers
ORDER BY
    first_name,
    last_name;

插入行的顶部百分比

INSERT TOP (10) PERCENT  
INTO sales.addresses (street, city, state, zip_code) 
SELECT
    street,
    city,
    state,
    zip_code
FROM
    sales.customers
ORDER BY
    first_name,
    last_name;