use master
go

create database lookup_test
go

USE [lookup_test]
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Fact_Test_Dim_TestCode1]') AND parent_object_id = OBJECT_ID(N'[dbo].[Fact_Test]'))
ALTER TABLE [dbo].[Fact_Test] DROP CONSTRAINT [FK_Fact_Test_Dim_TestCode1]
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Fact_Test_Dim_TestCode2]') AND parent_object_id = OBJECT_ID(N'[dbo].[Fact_Test]'))
ALTER TABLE [dbo].[Fact_Test] DROP CONSTRAINT [FK_Fact_Test_Dim_TestCode2]
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Fact_Test_Dim_TestCode3]') AND parent_object_id = OBJECT_ID(N'[dbo].[Fact_Test]'))
ALTER TABLE [dbo].[Fact_Test] DROP CONSTRAINT [FK_Fact_Test_Dim_TestCode3]
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Fact_Test_Dim_TestCode4]') AND parent_object_id = OBJECT_ID(N'[dbo].[Fact_Test]'))
ALTER TABLE [dbo].[Fact_Test] DROP CONSTRAINT [FK_Fact_Test_Dim_TestCode4]
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Fact_Test_Dim_TestCode5]') AND parent_object_id = OBJECT_ID(N'[dbo].[Fact_Test]'))
ALTER TABLE [dbo].[Fact_Test] DROP CONSTRAINT [FK_Fact_Test_Dim_TestCode5]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Fact_Test]') AND type in (N'U'))
DROP TABLE [dbo].[Fact_Test]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Dim_TestCode5]') AND type in (N'U'))
DROP TABLE [dbo].[Dim_TestCode5]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Dim_TestCode4]') AND type in (N'U'))
DROP TABLE [dbo].[Dim_TestCode4]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Dim_TestCode3]') AND type in (N'U'))
DROP TABLE [dbo].[Dim_TestCode3]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Dim_TestCode2]') AND type in (N'U'))
DROP TABLE [dbo].[Dim_TestCode2]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Dim_TestCode1]') AND type in (N'U'))
DROP TABLE [dbo].[Dim_TestCode1]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Dim_TestCode5]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Dim_TestCode5](
        [CodeKey5] [tinyint] NOT NULL,
        [CodeNM5] [varchar](20) NULL,
 CONSTRAINT [PK_Dim_TestCode5] PRIMARY KEY CLUSTERED 
(
        [CodeKey5] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Dim_TestCode4]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Dim_TestCode4](
        [CodeKey4] [tinyint] NOT NULL,
        [CodeNM4] [varchar](20) NULL,
 CONSTRAINT [PK_Dim_TestCode4] PRIMARY KEY CLUSTERED 
(
        [CodeKey4] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Dim_TestCode3]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Dim_TestCode3](
        [CodeKey3] [tinyint] NOT NULL,
        [CodeNM3] [varchar](20) NULL,
 CONSTRAINT [PK_Dim_TestCode3] PRIMARY KEY CLUSTERED 
(
        [CodeKey3] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Dim_TestCode2]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Dim_TestCode2](
        [CodeKey2] [tinyint] NOT NULL,
        [CodeNM2] [varchar](20) NULL,
 CONSTRAINT [PK_Dim_TestCode2] PRIMARY KEY CLUSTERED 
(
        [CodeKey2] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Dim_TestCode1]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Dim_TestCode1](
        [CodeKey1] [tinyint] NOT NULL,
        [CodeNM1] [varchar](20) NULL,
 CONSTRAINT [PK_Dim_TestCode1] PRIMARY KEY CLUSTERED 
(
        [CodeKey1] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_PADDING OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Fact_Test]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Fact_Test](
        [CodeKey1] [tinyint] NULL,
        [CodeKey2] [tinyint] NULL,
        [CodeKey3] [tinyint] NULL,
        [CodeKey4] [tinyint] NULL,
        [CodeKey5] [tinyint] NULL,
        [Cnt] [int] NULL
) ON [PRIMARY]
END
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Fact_Test_Dim_TestCode1]') AND parent_object_id = OBJECT_ID(N'[dbo].[Fact_Test]'))
ALTER TABLE [dbo].[Fact_Test]  WITH CHECK ADD  CONSTRAINT [FK_Fact_Test_Dim_TestCode1] FOREIGN KEY([CodeKey1])
REFERENCES [dbo].[Dim_TestCode1] ([CodeKey1])
GO
ALTER TABLE [dbo].[Fact_Test] CHECK CONSTRAINT [FK_Fact_Test_Dim_TestCode1]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Fact_Test_Dim_TestCode2]') AND parent_object_id = OBJECT_ID(N'[dbo].[Fact_Test]'))
ALTER TABLE [dbo].[Fact_Test]  WITH CHECK ADD  CONSTRAINT [FK_Fact_Test_Dim_TestCode2] FOREIGN KEY([CodeKey2])
REFERENCES [dbo].[Dim_TestCode2] ([CodeKey2])
GO
ALTER TABLE [dbo].[Fact_Test] CHECK CONSTRAINT [FK_Fact_Test_Dim_TestCode2]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Fact_Test_Dim_TestCode3]') AND parent_object_id = OBJECT_ID(N'[dbo].[Fact_Test]'))
ALTER TABLE [dbo].[Fact_Test]  WITH CHECK ADD  CONSTRAINT [FK_Fact_Test_Dim_TestCode3] FOREIGN KEY([CodeKey3])
REFERENCES [dbo].[Dim_TestCode3] ([CodeKey3])
GO
ALTER TABLE [dbo].[Fact_Test] CHECK CONSTRAINT [FK_Fact_Test_Dim_TestCode3]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Fact_Test_Dim_TestCode4]') AND parent_object_id = OBJECT_ID(N'[dbo].[Fact_Test]'))
ALTER TABLE [dbo].[Fact_Test]  WITH CHECK ADD  CONSTRAINT [FK_Fact_Test_Dim_TestCode4] FOREIGN KEY([CodeKey4])
REFERENCES [dbo].[Dim_TestCode4] ([CodeKey4])
GO
ALTER TABLE [dbo].[Fact_Test] CHECK CONSTRAINT [FK_Fact_Test_Dim_TestCode4]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Fact_Test_Dim_TestCode5]') AND parent_object_id = OBJECT_ID(N'[dbo].[Fact_Test]'))
ALTER TABLE [dbo].[Fact_Test]  WITH CHECK ADD  CONSTRAINT [FK_Fact_Test_Dim_TestCode5] FOREIGN KEY([CodeKey5])
REFERENCES [dbo].[Dim_TestCode5] ([CodeKey5])
GO
ALTER TABLE [dbo].[Fact_Test] CHECK CONSTRAINT [FK_Fact_Test_Dim_TestCode5]
GO

use lookup_test
go

insert Dim_TestCode1 values(0, '�ڵ�10');
insert Dim_TestCode1 values(1, '�ڵ�11');
insert Dim_TestCode1 values(2, '�ڵ�12');
insert Dim_TestCode1 values(3, '�ڵ�13');
insert Dim_TestCode1 values(4, '�ڵ�14');
insert Dim_TestCode1 values(5, '�ڵ�15');
insert Dim_TestCode1 values(6, '�ڵ�16');
insert Dim_TestCode1 values(7, '�ڵ�17');
insert Dim_TestCode1 values(8, '�ڵ�18');
insert Dim_TestCode1 values(9, '�ڵ�19');
go


insert Dim_TestCode2 values(0, '�ڵ�20');
insert Dim_TestCode2 values(1, '�ڵ�21');
insert Dim_TestCode2 values(2, '�ڵ�22');
insert Dim_TestCode2 values(3, '�ڵ�23');
insert Dim_TestCode2 values(4, '�ڵ�24');
insert Dim_TestCode2 values(5, '�ڵ�25');
insert Dim_TestCode2 values(6, '�ڵ�26');
insert Dim_TestCode2 values(7, '�ڵ�27');
insert Dim_TestCode2 values(8, '�ڵ�28');
insert Dim_TestCode2 values(9, '�ڵ�29');
go

insert Dim_TestCode3 values(0, '�ڵ�30');
insert Dim_TestCode3 values(1, '�ڵ�31');
insert Dim_TestCode3 values(2, '�ڵ�32');
insert Dim_TestCode3 values(3, '�ڵ�33');
insert Dim_TestCode3 values(4, '�ڵ�34');
insert Dim_TestCode3 values(5, '�ڵ�35');
insert Dim_TestCode3 values(6, '�ڵ�36');
insert Dim_TestCode3 values(7, '�ڵ�37');
insert Dim_TestCode3 values(8, '�ڵ�38');
insert Dim_TestCode3 values(9, '�ڵ�39');
go

insert Dim_TestCode4 values(0, '�ڵ�40');
insert Dim_TestCode4 values(1, '�ڵ�41');
insert Dim_TestCode4 values(2, '�ڵ�42');
insert Dim_TestCode4 values(3, '�ڵ�43');
insert Dim_TestCode4 values(4, '�ڵ�44');
insert Dim_TestCode4 values(5, '�ڵ�45');
insert Dim_TestCode4 values(6, '�ڵ�46');
insert Dim_TestCode4 values(7, '�ڵ�47');
insert Dim_TestCode4 values(8, '�ڵ�48');
insert Dim_TestCode4 values(9, '�ڵ�49');
go

insert Dim_TestCode5 values(0, '�ڵ�50');
insert Dim_TestCode5 values(1, '�ڵ�51');
insert Dim_TestCode5 values(2, '�ڵ�52');
insert Dim_TestCode5 values(3, '�ڵ�53');
insert Dim_TestCode5 values(4, '�ڵ�54');
insert Dim_TestCode5 values(5, '�ڵ�55');
insert Dim_TestCode5 values(6, '�ڵ�56');
insert Dim_TestCode5 values(7, '�ڵ�57');
insert Dim_TestCode5 values(8, '�ڵ�58');
insert Dim_TestCode5 values(9, '�ڵ�59');
go


with temp(num)
as
(
        select 0 num
        union all
        select num + 1 from temp
        where num + 1 < 10   
)
insert dbo.Fact_Test(CodeKey1, CodeKey2, CodeKey3, CodeKey4, CodeKey5, Cnt)
select
        rownum%9 CodeKey1
,       rownum%8 CodeKey2
,       rownum%7 CodeKey3
,       rownum%6 CodeKey4
,       rownum%5 CodeKey5
,       rownum Cnt
from (
        select 
               cast(
                       cast(g.num as varchar) +
                       cast(f.num as varchar) +
                       cast(e.num as varchar) +
                       cast(d.num as varchar) +
                       cast(c.num as varchar) +
                       cast(b.num as varchar) +
                       cast(a.num as varchar) as int) rownum
        from temp a, temp b, temp c, temp d, temp e, temp f, temp g
) t
option (maxrecursion 0)