紫糯米: 4月 2011

要從SQL SERVER中某table過濾出重複的資料後，塞入另外一個table裡的方法很多。這幾天工作的關係，發現其實也可利用 ignore_dup_key 這個 relation_index_option來取巧。下面我建立個簡單的範例說明：

--------------------------------------------------------------------------------

--建立uniqindex , 並指定ignore_dup_key

--------------------------------------------------------------------------------

--1. 建立target table，儲存無重複的資料

if exists (select name from sys.tables t where t.name = 'category')

drop table category

--2. 刪除既有的index

create table category(cat_desc varchar(50), cat_code varchar(10))

if exists(select name from sys.indexes i where i.name = 'idx_cat')

drop index idx_cat on cayrgory

--3. 建立unique index，並指定ignore_dup_key

create unique index idx_cat on category(cat_desc) with ignore_dup_key

--4. 建立1,000測試資料，其中5,000筆重複

declare @tb1 table(c1 varchar(50), c2 varchar(10))

declare @i int

declare @loop_start_date datetime, @loop_end_date datetime

declare @uniq_start_date datetime, @uniq_end_date datetime

set @i = 1

set @loop_start_date = GETDATE() --設定迴圈起始時間

while @i <= 10000

begin

if @i % 2 = 0

insert @tb1 values('test' + cast((@i-1) as varchar(50)), cast((@i-1) as varchar(10)))

else

insert @tb1 values('test' +cast(@i as varchar(50)), cast(@i as varchar(10)))

set @i = @i + 1

end

set @loop_end_date = GETDATE() --設定迴圈結束時間

set @uniq_start_date = GETDATE() --設定insert 至target table的起始時間

insert category(cat_desc, cat_code) select * from @tb1

set @uniq_end_date = GETDATE() --設定insert 至target table的結束時間

select 'inserting loop takes...'

select DATEDIFF(SECOND, @loop_start_date, @loop_end_date)

select 'inserting into uniq index with ignore_dup_key takes...'

select DATEDIFF(SECOND, @uniq_start_date, @uniq_end_date)

select * from @tb1 order by c1

select * from category order by cat_desc

一開始我先建立一個target table，以儲存待會測試資料中已經過濾出來的無重複資料。在target table上我們建立一個unique index，並以WITH IGNORE_DUP_KEY指定relational_index_option的值；接著宣告一個 table type的暫存資料表，並以迴圈塞入10,000筆資料，其中有5,000筆重複。最後我們直接將測試資料表insert 到target table中。這裡就是重點了。以往要是在建立索引時沒有加上 with ignore_dup_key這個選項，則insert到重複的資料時，就會得到一個錯誤的訊息，整個insert的transaction就會終止並且rollback：

訊息 2601，層級 14，狀態 1，行 8

無法以唯一索引 'idx_cat' 在物件 'dbo.category' 中插入重複的索引鍵資料列。

為了能略過該筆重複的資料所造成的錯誤並使insert動的繼續進行，create unique index時指定 with ignore_dup_key 即可辦到。以後如果懶得寫一堆過濾語法，或許可以偷個懶用用這個方法。

2011年4月10日星期日

紫糯米搬家了

利用IGNORE_DUP_KEY過濾重複的資料

2011年4月10日 星期日

紫糯米搬家了

利用IGNORE_DUP_KEY過濾重複的資料

2011年4月10日星期日