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

[color=blue]一、插入数据[/color]

[color=green]包括：select into、insert exec、插入新行、带有output的insert、序列机制[/color]

[color=green]1、select into[/color]
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>)}')

[color=green]2、insert exec[/color]

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

[color=green]3、插入新行[/color]
此处只讨论从源表向目标表插入数据,但只筛选目标表不包含其键值得行
[color=gray]--方案一： Insert New Rows From StageCusts[/color]
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);

[color=gray]--方案二： Insert New Customers From StageOrders using DISTINCT[/color]
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);

[color=gray]--方案三： Insert New Customers From StageOrders using MIN[/color]
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);

[color=gray]--方案四： Insert New Customers From StageOrders using Row Numbers[/color]
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

[color=green]4、带有output的insert[/color]
通过使用新的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

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

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

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

[color=gray]解决方案二：要求表中有唯一标识符[/color]
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

[color=gray]解决方案三：要求表中有唯一标识符[/color]
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';

[color=gray]解决方案四：用cte和row_number函数实现一种快速的解决方案[/color]

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
[color=green]3、基于联接的delete[/color]

--非标准查询
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
[color=green]4、带有output的delete[/color]

例子：
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

[color=blue]三、更新数据[/color]
[color=green]1、基于联接的update[/color]
例子：
[color=gray]方案一：[/color]
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';
[color=gray]方案二:[/color]
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;
[color=green]2、select和update赋值语句[/color]
赋值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
[color=gray]方案一：[/color]
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
[color=gray]方案二：[/color]
-- 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