4 關於資料倉儲維度資料處理的方法探究系列——緩慢變化維處理——覆蓋方式

CREATE TABLE t_dem_xxx

(

ID VARCHAR(20) NOT NULL,

Name1 VARCHAR(50),

Name2 VARCHAR(50),

CONSTRAINT PK_t_dem_xxx PRIMARY KEY (ID)

)

go

CREATE TABLE t_tmp_xxx

(

ID VARCHAR(20) NOT NULL,

Name1 VARCHAR(50),

Name2 VARCHAR(50),

CONSTRAINT PK_t_tmp_xxx PRIMARY KEY (ID)

)

go

CREATE PROCEDURE p_dem_xxx

AS

--維度抽取儲存過程

BEGIN

DECLARE

@num NUMERIC(10,0)

SELECT @num = COUNT(*) FROM t_dem_xxx

--如果原表為空，構造預設值

IF @num = 0

BEGIN

INSERT INTO t_dem_xxx (ID,Name1,Name2) SELECT '-2','NULL值',''

INSERT INTO t_dem_xxx (ID,Name1,Name2) SELECT '-1','缺失外來鍵',''

END

--根據主鍵插入在維度表中找不到的基礎資料

INSERT INTO t_dem_xxx

(

ID ,

Name1 ,

Name2

)

SELECT a.ID,a.Name1,a.Name2

FROM t_tmp_xxx a LEFT OUTER JOIN t_dem_xxx b

ON a.ID = b.ID

WHERE b.ID IS NULL

--根據主鍵更新原基礎表中變化的各屬性欄位

UPDATE t_dem_xxx

SET Name1 = a.Name1,

Name2 = a.Name2

FROM t_tmp_xxx A,t_dem_xxx B

WHERE a.ID = b.ID

AND b.ID NOT IN ('-1','-2')

END