T-SQL笔记2：INSERT、UPDATE和DELETE

T-SQL笔记2：INSERT、UPDATE和DELETE

本章摘要

1：显示向一个IDENTITY列插入值

2：在表中插入拥有UNIQUEIDENTIFIER列的行

3：使用INSERT……SELECT语句插入多行

4：调用存储过程插入数据

5：根据FROM和WHERE字句更新行

6：更新大值数据类型的列

7：使用OPENROWSET和BULK插入或更新图片文件

8：DELETE

9：截断表

10：使用TOP分块修改数据

 

1：显示向一个IDENTITY列插入值

     IDENTITY通常用作代理键（代理键是指由数据库生成的唯一的主键）。允许将显式值插入表的标识列中，必须使用SET IDENTITY_INSERT命令。

    语法：SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table { ON | OFF }

database_name 定的表所在的数据库的名称。

schema_name 所属架构的名称。

table 包含标识列的表的名称。

任何时候，一个会话中只有一个表的 IDENTITY_INSERT 属性可以设置为 ON。如果某个表已将此属性设置为 ON，则对另一个表发出 SET IDENTITY_INSERT ON 语句时，SQL Server 将返回一个错误信息，指出 SET IDENTITY_INSERT 已设置为 ON，并报告已将其属性设置为 ON 的表。

如果插入值大于表的当前标识值，则 SQL Server 自动将新插入值作为当前标识值使用。

     下面的语句假设ID为IDENTITY键，则会报错：

INSERT INTO dbo.Tool (ID, Name) VALUES (3, 'Garden shovel')

     要使得语句顺利执行，必须：

SET IDENTITY_INSERT dbo.Tool ON 

INSERT INTO dbo.Tool (ID, Name) VALUES (3, 'Garden shovel')

SET IDENTITY_INSERT dbo.Tool OFF

 

2：在表中插入拥有UNIQUEIDENTIFIER列的行

     UNIQUEIDENTIFIER数据类型存储了一个16字节的GUID。在INSERT的VALUES字句中使用NEWID()函数，可以得到这个GUID。

 

3：使用INSERT……SELECT语句插入多行

      使用INSERT...SELECT可以向表中插入多行。

     语法：INSERT [INTO] table_or_view_name [ (column_list) ] SELECT column_list FROM data_source

     如下列语句：

INSERT Shift_Archive (ShiftID, Name, StartTime, EndTime, ModifiedDate) SELECT ShiftID, Name, StartTime, EndTime, ModifiedDate FROM HumanResources.Shift ORDER BY ShiftID

 

4：调用存储过程插入数据

      既然能使用INSERT...SELECT向表中插入多行，那么使用存储过程也一样可以达到如此效果。

     语法：INSERT [INTO] table_or_view_name [ (column_list) ] EXEC stored_procedure_name [参数, …]

 

5：根据FROM和WHERE字句更新行

    直接看示例：假设Production.Product表中有一个产品名臣“LMJ”，对于每一位顾客只允许购买两个。为了达到这个查询的目的，任何购物车中只要这个产品的数量超过两个，都要立刻调整为所要求的数量：

UPDATE Sales.ShoppingCartItem
SET Quantity=2, ModifiedDate=GETDATE()
FROM Sales.ShoppingCartItem c
INNER JOIN Production.Product p ON
    c.ProductID = p.ProductID
WHERE p.Name='LMJ' AND c.Quantity > 2

 

6：更新大值数据类型的列

    SQLSERVER中带（MAX）的字段，均为大值数据类型。

    来看示例：

CREATE TABLE RecipeChapter
    (ChapterID int NOT NULL, Chapter varchar(max) NOT NULL)

INSERT RecipeChapter values( 1, '1234567890')

UPDATE RecipeChapter SET Chapter.WRITE('666',6, 3) WHERE ChapterID=1

    根据示例，我们知道，插入大值数据，跟普通的插入语法没有区别。不过，在更新大值数据的时候，SQLSERVER2005提供新的语法.WRITE来提高效率。

 

7：使用OPENROWSET和BULK插入或更新图片文件

    看示例，我们将系统中的图片文件插入或更新到表中：

CREATE TABLE StockGifs
    (StockGifID int NOT NULL,
     Gif varbinary(max) NOT NULL)

INSERT StockGifs (StockGifID, Gif)
SELECT 1, BulkColumn FROM OPENROWSET(BULK'C:\XXX.gif', SINGLE_BLOB) AS x

SELECT * FROM StockGifs WHERE StockGifID=1

 

8：DELETE

    示例：

    DELETE Production.Example_ProductProductPhoto WHERE ProductID NOT IN (SELECT ProductID FROM Production.Product)

 

9：截断表

    使用TRUNCATE可以截断表。由于它总是从表中删除所有的行，所以没有FROM和WHERE字句。示例：

--首先放入测试数据

SELECT * INTO Sales.Example_StoreContact FROM Sale.StoreContact

--然后截断

TRUNCATE TABLE Sales.Example_StoreContact

    注意：如果表有外键约束，则无法这样使用。

 

10：使用TOP分块修改数据

    使用TOP修改数据，能大幅提高性能并减少日志产出。示例：

WHILE( SELECT COUNT(*) FROM Production.Example_BillOfMaterials) > 0

    BEGIN

         DELETE TOP(500) FROM Production.Example_BillOfMaterials

    END