笔记-Microsoft SQL Server 2008技术内幕：T-SQL语言基础-08 数据修改

SQL Server 2008增强了VALUES语句的功能，允许在一条语句中指定由逗号分隔开的多行记录：

INSERT INTO dbo.Orders
  (orderid, orderdate, empid, custid)
VALUES
  (10003, ‘20090213‘, 4, ‘B‘),
  (10004, ‘20090214‘, 1, ‘A‘),
  (10005, ‘20090213‘, 1, ‘C‘),
  (10006, ‘20090215‘, 3, ‘C‘);

 在SQL Server 2008中还可以用VALUES来构建虚拟表：

SELECT *
FROM ( VALUES
         (10003, ‘20090213‘, 4, ‘B‘),
         (10004, ‘20090214‘, 1, ‘A‘),
         (10005, ‘20090213‘, 1, ‘C‘),
         (10006, ‘20090215‘, 3, ‘C‘) )
     AS O(orderid, orderdate, empid, custid);

 INSERT SELECT语句，注意，目标表必须事先存在。

INSERT INTO dbo.Orders(orderid, orderdate, empid, custid)
  SELECT orderid, orderdate, empid, custid
  FROM TSQLFundamentals2008.Sales.Orders
  WHERE shipcountry = ‘UK‘;

 INSERT EXEC语句

INSERT INTO dbo.Orders(orderid, orderdate, empid, custid)
  EXEC TSQLFundamentals2008.Sales.usp_getorders @country = ‘France‘;

 SELECT INTO语句

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

 SELECT INTO语句会复制来源表的基本结构（包括列名、数据类型、是否允许为NULL及IDENTITY属性）和数据。约束、索引及触发器不会从来源表中复制过来。

BULK INSERT语句

BULK INSERT dbo.Orders FROM ‘c:\temp\orders.txt‘
  WITH 
    (
       DATAFILETYPE    = ‘char‘,
       FIELDTERMINATOR = ‘,‘,
       ROWTERMINATOR   = ‘\n‘
    );

带有TOP选项的数据更新

以下代码将删除具有最小订单ID值的50个订单，而不是随机删除50行：

WITH C AS
(
  SELECT TOP(50) *
  FROM dbo.Orders
  ORDER BY orderid
)
DELETE FROM C;

OUTPUT子句

INSERT INTO dbo.T1(datacol)
  OUTPUT inserted.keycol, inserted.datacol
    SELECT lastname
    FROM TSQLFundamentals2008.HR.Employees
    WHERE country = N‘USA‘;

 OUTPUT子句为每个修改过的行返回一个输出行。如果有一个需求，出于审核的目的，需要把修改过的行的一个子集导入一个表：

INSERT INTO dbo.ProductsAudit(productid, colname, oldval, newval)
  SELECT productid, N‘unitprice‘, oldval, newval
  FROM (UPDATE dbo.Products
          SET unitprice *= 1.15
        OUTPUT 
          inserted.productid,
          deleted.unitprice AS oldval,
          inserted.unitprice AS newval
        WHERE SupplierID = 1) AS D
  WHERE oldval < 20.0 AND newval >= 20.0;

笔记-Microsoft SQL Server 2008技术内幕：T-SQL语言基础-08 数据修改

标签：