首页 > 代码库 > 插入数据

插入数据

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

USE tempdb;GOIF OBJECT_ID(dbo.Orders,U) IS NOT NULL DROP TABLE dbo.Orders;GO-- 创建表OrdersCREATE 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 = NFrance;    -- 方法四:SELECT INTO(创建目标表,用查询结果填充)IF OBJECT_ID(dbo.Orders,U) IS NOT NULL DROP TABLE dbo.Orders;GOSELECT orderid,orderdate,empid,custid INTO dbo.OrdersFROM TSQLFundamentals2008.Sales.Orders;-- 方法五:BULK INSERT(将文本文件中的数据导入到已存在的表中)BULK INSERT dbo.Orders FROM D:\mcgrady\temp\TSQLFundamentals20090201\orders.txtWITH (    DATAFILETYPE= char, --指定字符格式    FIELDTERMINATOR= ,, --指定字段终止符    ROWTERMINATOR= \n --指定行终止符);

 

插入数据