T-SQL查询学习笔记——数据修改

一、插入数据

包括：select into、insert exec、插入新行、带有output的insert、序列机制

1、select into
select into 语句不向调用者返回结果集，而是创建一个包含查询结果集的新表。新表的列沿用查询结果集的列名称、数据类型、可空性以及identity属性。select into 不从查询的源表复制约束、索引、触发器。如果你希望在表中包含与查询的源相同的索引、约束和触发器，你必须在执行完该语句以后再添加

如果源列包含identity属性,目标列中也会包含该属性.如果不想向目标列复制identity属性,对源列做些修改即可。
例如：OrderID+0 as OrderID

SELECT OrderID+0 AS OrderID, CustomerID, EmployeeID, OrderDate,
  RequiredDate, ShippedDate, ShipVia, Freight, ShipName,
  ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry
INTO dbo.MyOrders
FROM Northwind.dbo.Orders
WHERE 1 = 2;
GO

提示：假设你要把存储过程或动态批处理的结果集插入到新表，但又不知道它的架构。这时，你可以在select into 语句的from子句中指定openquery，并把自己的服务器看作是一个链接服务器：
exec sp_serveroption <your_server>,'data access',true;
select * into <target_table>
from openquery(<your_server>,'exec{<proc_name>|(<dynamic_batch>)}')

2、insert exec

insert into <target_table> exec{<proc_name>|(<dynamic_batch>)}

3、插入新行
此处只讨论从源表向目标表插入数据,但只筛选目标表不包含其键值得行
--方案一： Insert New Rows From StageCusts
INSERT INTO dbo.MyCustomers(CustomerID, CompanyName, ContactName,
    ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax)
  SELECT CustomerID, CompanyName, ContactName,
    ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax
  FROM dbo.StageCusts AS S
  WHERE NOT EXISTS
    (SELECT * FROM dbo.MyCustomers AS T
     WHERE T.CustomerID = S.CustomerID);

--方案二： Insert New Customers From StageOrders using DISTINCT
INSERT INTO dbo.MyCustomers(CustomerID, CompanyName, ContactName,
    ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax)
  SELECT DISTINCT CustomerID, CompanyName, ContactName,
    ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax
  FROM dbo.StageOrders AS S
  WHERE NOT EXISTS
    (SELECT * FROM dbo.MyCustomers AS T
     WHERE T.CustomerID = S.CustomerID);

--方案三： Insert New Customers From StageOrders using MIN
INSERT INTO dbo.MyCustomers(CustomerID, CompanyName, ContactName,
    ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax)
  SELECT CustomerID, CompanyName, ContactName,
    ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax
  FROM dbo.StageOrders AS S
  WHERE NOT EXISTS
    (SELECT * FROM dbo.MyCustomers AS T
     WHERE T.CustomerID = S.CustomerID)
    AND S.OrderID = (SELECT MIN(OrderID) FROM dbo.StageOrders AS S2
                     WHERE S2.CustomerID = S.CustomerID);

--方案四： Insert New Customers From StageOrders using Row Numbers
INSERT INTO dbo.MyCustomers(CustomerID, CompanyName, ContactName,
    ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax)
  SELECT CustomerID, CompanyName, ContactName,
    ContactTitle, Address, City, Region, PostalCode, Country, Phone, Fax
  FROM (SELECT
          ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY OrderID) AS rn,
          CustomerID, CompanyName, ContactName, ContactTitle, Address, City,
          Region, PostalCode, Country, Phone, Fax
        FROM dbo.StageOrders) AS S
  WHERE NOT EXISTS
    (SELECT * FROM dbo.MyCustomers AS T
     WHERE T.CustomerID = S.CustomerID)
    AND rn = 1;
GO

4、带有output的insert
通过使用新的output子句,Sql Server 2005支持从数据修改语句中返回输出
例子：
IF OBJECT_ID('dbo.CustomersDim') IS NOT NULL
  DROP TABLE dbo.CustomersDim;
GO

CREATE TABLE dbo.CustomersDim
(
  KeyCol      INT          NOT NULL IDENTITY PRIMARY KEY,
  CustomerID  NCHAR(5)     NOT NULL,
  CompanyName NVARCHAR(40) NOT NULL,
  /* ... other columns ... */
);

-- Insert New Customers and Get their Surrogate Keys
DECLARE @NewCusts TABLE
(
  CustomerID NCHAR(5) NOT NULL PRIMARY KEY,
  KeyCol     INT      NOT NULL UNIQUE
);

INSERT INTO dbo.CustomersDim(CustomerID, CompanyName)
    OUTPUT inserted.CustomerID, inserted.KeyCol
    INTO @NewCusts
    --OUTPUT inserted.CustomerID, inserted.KeyCol
  SELECT CustomerID, CompanyName
  FROM Northwind.dbo.Customers
  WHERE Country = N'UK';

SELECT CustomerID, KeyCol FROM @NewCusts;
GO

二、删除数据
1、truncate 和 delete
如果你要从表中移除所有行,应该使用truncate table,而不应该使用不带where子句的delete。delete总是被完整的记录到日志，而truncate table总是按最小方式被记录到日志

truncate table 和 delete 除了在性能方面有巨大差异外，它们对identity属性的处理方式也不同.truncate table 把identity属性重置为最初的种子，而delete则不会
2、移除包含重复数据的行

解决方案一：要求整行都重复
SELECT DISTINCT * INTO dbo.OrdersTmp FROM dbo.OrdersDups;
DROP TABLE dbo.OrdersDups;
EXEC sp_rename 'dbo.OrdersTmp', 'OrdersDups';

解决方案二：要求表中有唯一标识符
DELETE FROM dbo.OrdersDups
WHERE EXISTS
  (SELECT *
   FROM dbo.OrdersDups AS O2
   WHERE O2.OrderID = dbo.OrdersDups.OrderID
     AND O2.KeyCol > dbo.OrdersDups.KeyCol);
GO

解决方案三：要求表中有唯一标识符
SELECT O.OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate,
  ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity,
  ShipRegion, ShipPostalCode, ShipCountry
INTO dbo.OrdersTmp
FROM dbo.OrdersDups AS O
  JOIN (SELECT OrderID, MAX(KeyCol) AS mx
        FROM dbo.OrdersDups
        GROUP BY OrderID) AS U
    ON O.OrderID = U.OrderID
    AND O.KeyCol = U.mx;

DROP TABLE dbo.OrdersDups;
EXEC sp_rename 'dbo.OrdersTmp', 'OrdersDups';

解决方案四：用cte和row_number函数实现一种快速的解决方案

WITH Dups AS
(
  SELECT *,
    ROW_NUMBER() OVER(PARTITION BY OrderID ORDER BY OrderID) AS rn
  FROM dbo.OrdersDups
)
DELETE FROM Dups WHERE rn > 1;
GO
3、基于联接的delete

--非标准查询
BEGIN TRAN

DELETE FROM OD
FROM dbo.[Order Details] AS OD
  JOIN dbo.Orders AS O
    ON OD.OrderID = O.OrderID
WHERE O.OrderDate >= '19980506';

ROLLBACK TRAN

--标准查询
BEGIN TRAN

DELETE FROM dbo.[Order Details]
WHERE EXISTS
  (SELECT *
   FROM dbo.Orders AS O
   WHERE O.OrderID = dbo.[Order Details].OrderID
     AND O.OrderDate >= '19980506');

ROLLBACK TRAN
GO
4、带有output的delete

例子：
WHILE 1=1
BEGIN
  BEGIN TRAN
    DELETE TOP(5000) FROM dbo.LargeOrders
      OUTPUT deleted.* INTO dbo.OrdersArchive
    WHERE OrderDate < '20010101';

    IF @@rowcount < 5000
    BEGIN
      COMMIT TRAN
      BREAK;
    END
  COMMIT TRAN
END
GO

三、更新数据
1、基于联接的update
例子：
方案一：
  UPDATE O
    SET ShipCountry = C.Country,
        ShipRegion = C.Region,
        ShipCity = C.City
  FROM dbo.Orders AS O
    JOIN dbo.Customers AS C
      ON O.CustomerID = C.CustomerID
  WHERE C.Country = 'USA';
方案二:
WITH UPD_CTE AS
(
  SELECT
    O.ShipCountry AS set_Country, C.Country AS get_Country,
    O.ShipRegion  AS set_Region,  C.Region  AS get_Region,
    O.ShipCity    AS set_City,    C.City    AS get_City
  FROM dbo.Orders AS O
    JOIN dbo.Customers AS C
      ON O.CustomerID = C.CustomerID
  WHERE C.Country = 'USA'
)
UPDATE UPD_CTE
  SET set_Country = get_Country,
      set_Region  = get_Country,
      set_City    = get_City;
2、select和update赋值语句
赋值select
如果表中没有记录则为null，如果有多条记录则为最后一跳记录的值
DECLARE @EmpID AS INT, @Pattern AS NVARCHAR(100);

SET @Pattern = N'Davolio'; -- Try also N'Ben-Gan', N'D%';
SET @EmpID = 999;

SET @EmpID = (SELECT EmployeeID
              FROM dbo.Employees
              WHERE LastName LIKE @Pattern);

SELECT @EmpID;
GO

赋值update
CREATE TABLE dbo.T1
(
  col1 INT        NOT NULL,
  col2 VARCHAR(5) NOT NULL
);

INSERT INTO dbo.T1(col1, col2) VALUES(0, 'A');
INSERT INTO dbo.T1(col1, col2) VALUES(0, 'B');
INSERT INTO dbo.T1(col1, col2) VALUES(0, 'C');
INSERT INTO dbo.T1(col1, col2) VALUES(0, 'C');
INSERT INTO dbo.T1(col1, col2) VALUES(0, 'C');
INSERT INTO dbo.T1(col1, col2) VALUES(0, 'B');
INSERT INTO dbo.T1(col1, col2) VALUES(0, 'A');
INSERT INTO dbo.T1(col1, col2) VALUES(0, 'A');
INSERT INTO dbo.T1(col1, col2) VALUES(0, 'C');
INSERT INTO dbo.T1(col1, col2) VALUES(0, 'C');
go
方案一：
DECLARE @i AS INT;
SET @i = 0;
UPDATE dbo.T1 SET @i = col1 = @i + 1;
逻辑上，这个set语句等价于set col1=@i+1,@i=@i+1
在update语句中，你无法控制T1中的行被扫描和修改的顺序
GO
方案二：
-- SQL Server 2005
WITH T1RN AS
(
  SELECT col1, ROW_NUMBER() OVER(ORDER BY col2) AS RowNum
  FROM dbo.T1
)
UPDATE T1RN SET col1 = RowNum;
GO