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;

 

分类: SQL Server