SQL 中 INSERT 语句的语法

    <p>INSERT 语句的整体语法结构如下所示：</p>

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;