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)