本文是SQLServer数据修改语句系列的第二篇文章。第一篇文章是《SQL Server数据修改语句入门》。所有示例均基于Microsoft SQL Server Management Studio和AdventureWorks2012数据库。可以通过《SQL Server使用入门指南》来开始使用这些免费工具。
尽管本文使用AdventureWorks数据库进行示例,但决定在数据库中创建一个示例表来更好地说明示例。可以在这里找到需要运行的脚本。
INSERT语句用于向表中添加行。尽管插入语句可以从许多来源插入数据,如字面值或源值,但基本格式是相同的。INSERT语句有三个组成部分:
插入语句的一般格式是:
INSERT INTO tableName (column1, column2, …) VALUES (value1, value2, …)
现在,将进行一些示例插入。如果还没有这样做,请运行脚本来创建esqlSalesPerson表。
在这个示例中,向esqlSalesPerson表插入一行。这里是它的表结构:
INSERT INTO dbo.esqlSalesPerson (FullName, SalesLastYear, City, rowguid) VALUES ('George Hitson', NULL, 'Midland', '794310D2-6293-4259-AC11-71D96689A3DD')
注意没有在列列表中指定SalesPersonID。这是因为,作为标识值,该列会自动填充。
可以交换列的顺序;它们不必按顺序排列。同时注意并非所有列都被指定:
INSERT INTO dbo.esqlSalesPerson (City, FullName, rowguid) VALUES ('Traverse City', 'Donald Sax', 'F6E26EFD-5838-40F8-ABB3-D487D2932873')
创建了以下行:
由于SalesLastYear没有指定,它被设置为NULL:
行的列值用括号()括起来。要插入多行,只需包含另一组列值。只要确保用逗号分隔每组即可:
INSERT INTO dbo.esqlSalesPerson (City, FullName, rowguid) VALUES ('Bay City', 'Ralph Gitter', 'DED7DB59-7149-47DD-8D8F-D5FCFFF11124'), ('Alpena', 'Mike Orange', '94600A1E-DD83-4ACE-9D59-8CD727A2C83E')
请注意,当向数据类型为CHAR、VARCHAR或VARBINARY的列插入数据时,数据的填充或截断取决于SET ANSI_PADDING设置。
当SET ANSI_PADDING OFF时,CHAR数据类型用空格填充,VARCHAR数据类型删除尾随空格,VARBINARY删除尾随零。
例如,如果一个字段定义为CHAR(10),将值‘Kris’插入此列,那么它将用六个空格填充。插入的值是‘Kris’。
可以使用TRY…CATCH结构在执行INSERT语句时处理错误。INSERT语句可能失败的几个常见原因包括:
在这些情况下,INSERT语句执行停止,INSERT生成错误。即使那些没有生成错误的行也不会插入到表中。
这种“全部或无”的行为可以修改为算术错误。考虑除以零的错误。
INSERT INTO myNumbers (x, y) VALUES (10/0, 5), (10/5, 2), (10/2, 5)
如果SET ARITHABORT设置为ON,这将生成错误。在这种情况下,插入停止,没有行被插入,并且抛出错误。
然而,如果SET ARITHABORT设置为OFF并且ANSI_WARNINGS设置为OFF,那么相同的语句将成功完成。如果有数学错误,结果将被替换为NULL。
例如:
SET ARITHABORT OFF SET ANSI_WARNINGS OFF INSERT INTO myNumbers (x, y) VALUES (10/0, 5), (10/5, 2), (10/2, 5)
添加三行,值如下:
当向声明为uniqueidentifier类型的列添加数据时,请使用NEWID()函数生成一个全局唯一值。
INSERT INTO dbo.esqlSalesPerson (City, FullName, rowguid) VALUES ('Traverse City', 'Donald Sax', NEWID())
将新行插入esqlSalesPerson。如果再次运行命令,将添加另一行,但rowguid值是不同的。
每次调用NEWID()时,都会生成一个不同的值。
每当向具有身份列属性的表插入一行时,该行的列都会生成一个新的值。由于esqlSalesPerson.SalesPersonID是身份列,不在INSERT语句中指定它。每次添加一行时,身份值都会增加一个并添加到行中。
如果尝试使用自己的值插入一行,将抛出一个错误。
INSERT INTO dbo.esqlSalesPerson (SalesPersonID, City, FullName, rowguid) VALUES (9999, 'Traverse City', 'Donald Sax', NEWID())
生成错误:
Cannot insert explicit value for identity column in table 'esqlSalesPerson' when IDENTITY_INSERT is set to OFF.
要解决这个问题,可以SET IDENTITY_INSERT ON:
SET IDENTITY_INSERT esqlSalesPerson ON; INSERT INTO dbo.esqlSalesPerson (SalesPersonID, City, FullName, rowguid) VALUES (9999, 'Traverse City', 'Donald Sax', NEWID())
运行时不会抛出错误。
插入行时,任何未指定的列都由DBMS提供值;否则行无法加载。DBMS自动为列提供值的情况如下:
如果语句没有提供值,引擎也无法提供值,那么行就无法插入。这通常发生在缺少值且列是NOT NULL的情况下。
还可以使用INSERT语句将一个或多个行从一个表插入到另一个表中。实现这一点的一种方法是使用SELECT语句的结果为INSERT语句提供值。
一般形式是:
INSERT INTO targetTable (column1, column2, …) SELECT (column1, column2, …) FROM sourceTable
假设AdventureWorks销售经理想要创建一个SalesPerson表,并且只想包括去年销售额超过100万美元的销售人员。
要填充这个表,可以运行:
INSERT INTO esqlSalesPerson (FullName, SalesLastYear, rowguid) SELECT P.FirstName + ' ' + P.LastName, S.SalesLastYear, NEWID() FROM Sales.SalesPerson S INNER JOIN Person.Person P ON P.BusinessEntityID = S.BusinessEntityID WHERE S.SalesLastYear > 1000000
为了使其正常工作,SELECT语句返回的列必须与INSERT列列表中指定的顺序相同。在这个示例中,请注意rowguid是一个必需字段。要填充这个值,使用NEWID()函数。
还可以使用公共表表达式(CTE)来定义要插入的行。上述示例以CTE(公共表表达式)编写如下:
WITH topSalesPerson (FullName, SalesLastYear, rowguid) AS (SELECT P.FirstName + ' ' + P.LastName, S.SalesLastYear, NEWID() FROM Sales.SalesPerson S INNER JOIN Person.Person P ON P.BusinessEntityID = S.BusinessEntityID WHERE S.SalesLastYear > 1000000) INSERT INTO esqlSalesPerson (FullName, SalesLastYear, rowguid) SELECT FullName, SalesLastYear, rowguid FROM topSalesPerson