8 关于数据仓库维度数据处理的方法探究系列——父子维

--父子维度表

CREATE TABLE t_dem_xxx

(

IDVARCHAR(20) NOT NULL,

SuperIDVARCHAR(20) NOT NULL,

NameVARCHAR(50)

CONSTRAINT PK_t_dem_xxx PRIMARY KEY (SurID)

)

go

CREATE TABLE t_tmp_xxx

(

IDVARCHAR(20) NOT NULL,

SuperIDVARCHAR(20) NOT NULL,

NameVARCHAR(50)

CONSTRAINT PK_t_tmp_xxx PRIMARY KEY (ID)

)

Go

CREATE PROCEDURE p_dem_xxx

AS

--维度抽取存储过程

BEGIN

DECLARE

@numNUMERIC(10,0)

SELECT @num = COUNT(*) FROM t_dem_xxx

--如果原表为空，构造缺省值

IF @num = 0

BEGIN

INSERT INTO t_dem_xxx (ID,SuperID,Name) SELECT '-2','0','NULL值'

INSERT INTO t_dem_xxx (ID,SuperID,Name) SELECT '-1','0','缺失外键'

END

--根据主键插入在维度表中找不到的基础数据

INSERT INTO t_dem_xxx

(

ID,

SuperID,

Name

)

SELECT a.ID,a.SuperID,a.Name

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 SuperID = a.SuperID,

Name = a.Name

FROM t_tmp_xxx A,t_dem_xxx B

WHERE a.ID = b.ID

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

END
1s{0M&R ]0