插入数据

　　我们可以通过以下几种方式向表中插入数据。分别是INSERT VALUES，INSERT SELECT，INSERT EXEC，SELECT INTO和BULK INSERT共五种方法。为了演示，我们先在数据库tempdb中创建表dbo.Orders，然后分别用这几种方法向表中插入数据，下面是示例代码。

USE tempdb;
GO

IF OBJECT_ID('dbo.Orders','U') IS NOT NULL DROP TABLE dbo.Orders;
GO

-- 创建表Orders
CREATE TABLE dbo.Orders
(
    OrderID INT NOT NULL,
    OrderDate DATETIME NOT NULL
        CONSTRAINT DFT_OrderDate DEFAULT(CURRENT_TIMESTAMP),
    EmpID INT NOT NULL,
    CustID VARCHAR(20) NOT NULL,
    CONSTRAINT PK_Orders PRIMARY KEY(OrderID)
);

-- 插入数据
-- 方法一：INSERT VALUES(基于指定值的行)
INSERT INTO dbo.Orders ( OrderID, OrderDate, EmpID, CustID )
VALUES ( 10001, '20140917',1,'A'),
        (10002,'20140916',2,'B'),
        (10003,'20140915',3,'C'),
        (10004,'20140914',4,'D');
        
        
-- 方法二：INSERT SELECT(基于SELECT查询返回行，基于已有表)
INSERT INTO dbo.Orders ( OrderID, OrderDate, EmpID, CustID )
    SELECT orderid,orderdate,empid,custid FROM TSQLFundamentals2008.Sales.Orders
    WHERE shipcountry='UK';
    
-- 方法三：INSERT EXEC(基于存储过程或动态SQL返回结果，基于已有表)
INSERT INTO dbo.Orders ( OrderID, OrderDate, EmpID, CustID )
    EXEC TSQLFundamentals2008.dbo.usp_GetOrders @Country = N'France';
    
-- 方法四：SELECT INTO(创建目标表，用查询结果填充，创建新的表)
IF OBJECT_ID('dbo.Orders','U') IS NOT NULL DROP TABLE dbo.Orders;
GO

SELECT orderid,orderdate,empid,custid 
INTO dbo.Orders
FROM TSQLFundamentals2008.Sales.Orders;

-- 方法五：BULK INSERT(将文本文件中的数据导入到已存在的表中，基于已有表)
BULK INSERT dbo.Orders FROM 'D:\mcgrady\temp\TSQLFundamentals20090201\orders.txt'
WITH 
(
    DATAFILETYPE= 'char', --指定字符格式
    FIELDTERMINATOR= ',', --指定字段终止符
    ROWTERMINATOR= '\n' --指定行终止符
);