INSERT 语句的整体语法结构如下所示:
INSERT [TOP (<expression>) [PERCENT]] [INTO] <tabular object> [(<column list>]
[OUTPUT <output clause>]
{VALUES (<data values> [,(<data values>] [, ...n]
| <table source>
| EXEC <procedure>
| DEFAULT VALUES
这个结构看起来有些繁杂,它的更基本的语法结构如下:
INSERT [INTO] <table>
[(<column list>)]
VALUES (<data values>) [, (<data values>)] [, ...n]
INTO 关键字,是可选的。它纯粹只是为了增加可读性而存在的。
在 SQL Server 2008 中,可以一次插入多行。要实现这点,只需要添加额外的用逗号分隔的插入值,如下所示:
INSERT INTO Sales
(StoreCode, OrderNumber, OrderDate, Quantity, Terms, TitleID)
VALUES
('TST2', 'TESTORDER2', '01/01/1999', 10, 'NET 30', 1234567),
('TST2', 'TESTORDER3', '02/01/1999', 10, 'NET 30', 1234567);
在SQL Server 2008之前,要想多行插入,客户端应用程序必须为要插入的每条数据行单独发出插入命令。不过也有其他需要开发人员思考和努力后才能想到的方法,比如可以使用下面这个变通的方法来实现一次多行插入:
INSERT INTO Sales
(StoreCode, OrderNumber, OrderDate, Quantity, Terms, TitleID)
SELECT 'TST2', 'TESTORDER4', '01/01/1999', 10, 'NET 30', 1234567
UNION
SELECT 'TST2', 'TESTORDER5', '02/01/1999', 10, 'NET 30', 1234567
将别的表中的已有数据插入到指定的表中的示例:
/* This next statement is going to use code to change the "current" database
** to AdventureWorks2008. This makes certain, right in the code that we are
** going to the correct database.
*/
USE AdventureWorksLT2008;
/* This next statement declares our working table.
** This particular table is actually a variable we are declaring on the fly.
*/
DECLARE @MyTable Table (
SalesOrderID int,
CustomerID char(5)
);
/* Now that we have our table variable, we're ready to populate it with data
** from our SELECT statement. Note that we could just as easily insert the
** data into a permanent table (instead of a table variable).
*/
INSERT INTO @MyTable
SELECT SAlesOrderID, CustomerID
FROM AdventureWorksLT2008.SalesLT.SalesOrderHeader ;
-- Finally, let's make sure that the data was inserted like we think
SELECT *
FROM @MyTable;