多個前端應用程式需同時取到唯一編號後,新增到另一個資料表

以往需包在交易內,先更新一筆記錄後,再以 Select 查詢傳回,以兩句語法加上交易取得唯一序號,現在可以透過 Output 子句一句話完成

改自 TechEd 2009 US DAT313 的 Slide

–建立測試用資料表

USE tempdb
GO

–產生唯一編號的資料表
CREATE TABLE tbSequence(ID INT)
INSERT tbSequence VALUES(1)
GO

–存放唯一序號的資料表
CREATE TABLE tbNeedUniqueSequence(ID INT,BatchNo TINYINT,InsTime TIME(7) DEFAULT(SYSDATETIME()))
GO

–透過預存程序新增資料
CREATE PROC spAdd @i INT,@BatchNo TINYINT
AS
UPDATE dbo.tbSequence
  SET ID += @i
OUTPUT inserted.ID, @BatchNo
  INTO dbo.tbNeedUniqueSequence(ID, BatchNo);

GO

SELECT * FROM tbNeedUniqueSequence ORDER BY ID
TRUNCATE TABLE tbNeedUniqueSequence
UPDATE tbSequence SET ID=0
Go

–另一種解法
ALTER PROC spAdd @i INT,@BatchNo TINYINT
AS
INSERT INTO dbo.tbNeedUniqueSequence(ID, BatchNo)
  SELECT ID, @BatchNo
  FROM (UPDATE dbo.tbSequence
          SET  ID += @i
        OUTPUT inserted.ID) AS D;

 

–測試用的 Batch,存檔成 TestBatch.sql

USE tempdb
DECLARE @i INT=0
WHILE @i<10000
BEGIN
    EXEC spAdd 1,$(BatchNo)
    SET @i+=1
END

 

最後以命令提示列的批次檔測試,批次檔內容如下:

start sqlcmd -E -i TestBatch.sql -vBatchNo=1
start sqlcmd -E -i TestBatch.sql -vBatchNo=2
start sqlcmd -E -i TestBatch.sql -vBatchNo=3
start sqlcmd -E -i TestBatch.sql -vBatchNo=4

執行結果如下

image

第一種方式 2005/2008 皆可用,第二種方式似乎 2005 會有問題

另外,Row_Number()函數在取值時,其 Order By 子句可以是 SELECT 常數,例如:

Row_Number() OVER(Order By (Select NULL))

One Comment

  1. Posted 2009 年 06 月 26 日 at 20:26:05 | Permalink | 回覆

    这样写确实很巧妙。

發表迴響

在下方填入你的資料或按右方圖示以社群網站登入:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 變更 )

Twitter picture

You are commenting using your Twitter account. Log Out / 變更 )

Facebook照片

You are commenting using your Facebook account. Log Out / 變更 )

Google+ photo

You are commenting using your Google+ account. Log Out / 變更 )

連結到 %s

%d 位部落客按了讚: