CREATE TABLE #Primes (CardId tinyint, PrimeFactor tinyint)

SET NOCOUNT ON

INSERT INTO #Primes (CardId, PrimeFactor) VALUES (1,2)

INSERT INTO #Primes (CardId, PrimeFactor) VALUES (2,3)

INSERT INTO #Primes (CardId, PrimeFactor) VALUES (3,5)

INSERT INTO #Primes (CardId, PrimeFactor) VALUES (4,7)

INSERT INTO #Primes (CardId, PrimeFactor) VALUES (5,11)

INSERT INTO #Primes (CardId, PrimeFactor) VALUES (6,13)

INSERT INTO #Primes (CardId, PrimeFactor) VALUES (7,17)

INSERT INTO #Primes (CardId, PrimeFactor) VALUES (8,19)

INSERT INTO #Primes (CardId, PrimeFactor) VALUES (9,23)

INSERT INTO #Primes (CardId, PrimeFactor) VALUES (10,29)

INSERT INTO #Primes (CardId, PrimeFactor) VALUES (11,31)

INSERT INTO #Primes (CardId, PrimeFactor) VALUES (12,37)

INSERT INTO #Primes (CardId, PrimeFactor) VALUES (13,41)

INSERT INTO #Primes (CardId, PrimeFactor) VALUES (14,43)

INSERT INTO #Primes (CardId, PrimeFactor) VALUES (15,47)

INSERT INTO #Primes (CardId, PrimeFactor) VALUES (16,53)

INSERT INTO #Primes (CardId, PrimeFactor) VALUES (17,59)

INSERT INTO #Primes (CardId, PrimeFactor) VALUES (18,61)

INSERT INTO #Primes (CardId, PrimeFactor) VALUES (19,67)

INSERT INTO #Primes (CardId, PrimeFactor) VALUES (20,71)

INSERT INTO #Primes (CardId, PrimeFactor) VALUES (21,73)

INSERT INTO #Primes (CardId, PrimeFactor) VALUES (22,79)

INSERT INTO #Primes (CardId, PrimeFactor) VALUES (23,83)

INSERT INTO #Primes (CardId, PrimeFactor) VALUES (24,89)

INSERT INTO #Primes (CardId, PrimeFactor) VALUES (25,97)

INSERT INTO #Primes (CardId, PrimeFactor) VALUES (26,101)

INSERT INTO #Primes (CardId, PrimeFactor) VALUES (27,103)

INSERT INTO #Primes (CardId, PrimeFactor) VALUES (28,107)

INSERT INTO #Primes (CardId, PrimeFactor) VALUES (29,109)

INSERT INTO #Primes (CardId, PrimeFactor) VALUES (30,113)

INSERT INTO #Primes (CardId, PrimeFactor) VALUES (31,127)

INSERT INTO #Primes (CardId, PrimeFactor) VALUES (32,131)

INSERT INTO #Primes (CardId, PrimeFactor) VALUES (33,137)

INSERT INTO #Primes (CardId, PrimeFactor) VALUES (34,139)

INSERT INTO #Primes (CardId, PrimeFactor) VALUES (35,149)

INSERT INTO #Primes (CardId, PrimeFactor) VALUES (36,151)

INSERT INTO #Primes (CardId, PrimeFactor) VALUES (37,157)

INSERT INTO #Primes (CardId, PrimeFactor) VALUES (38,163)

INSERT INTO #Primes (CardId, PrimeFactor) VALUES (39,167)

INSERT INTO #Primes (CardId, PrimeFactor) VALUES (40,173)

INSERT INTO #Primes (CardId, PrimeFactor) VALUES (41,179)

INSERT INTO #Primes (CardId, PrimeFactor) VALUES (42,181)

INSERT INTO #Primes (CardId, PrimeFactor) VALUES (43,191)

INSERT INTO #Primes (CardId, PrimeFactor) VALUES (44,193)

INSERT INTO #Primes (CardId, PrimeFactor) VALUES (45,197)

INSERT INTO #Primes (CardId, PrimeFactor) VALUES (46,199)

INSERT INTO #Primes (CardId, PrimeFactor) VALUES (47,211)

INSERT INTO #Primes (CardId, PrimeFactor) VALUES (48,223)

INSERT INTO #Primes (CardId, PrimeFactor) VALUES (49,227)

INSERT INTO #Primes (CardId, PrimeFactor) VALUES (50,229)

INSERT INTO #Primes (CardId, PrimeFactor) VALUES (51,233)

INSERT INTO #Primes (CardId, PrimeFactor) VALUES (52,239)

GO

IF OBJECT_ID('Dim_Deck') IS NOT NULL DROP TABLE Dim_Deck

GO

CREATE TABLE Dim_Deck (

CardId tinyint identity(1,1) primary key,

PrimaryFactor tinyint unique NOT NULL,

SuitTxt varchar(10) NOT NULL,

SuitNum tinyint NOT NULL,

RankTxt char(1) NOT NULL,

RankNum tinyint NOT NULL,

CardTxt char(2) NOT NULL

)

GO

DECLARE @CardId tinyint; SET @CardId = 1

DECLARE @SuitCntr tinyint; SET @SuitCntr = 1

WHILE @SuitCntr <= 4

BEGIN

DECLARE @SuitTxt varchar(10)

IF @SuitCntr = 1 SET @SuitTxt = 'Clubs'

ELSE IF @SuitCntr = 2 SET @SuitTxt = 'Diamonds'

ELSE IF @SuitCntr = 3 SET @SuitTxt = 'Hearts'

ELSE IF @SuitCntr = 4 SET @SuitTxt = 'Spades'

ELSE SET @SuitTxt = NULL

DECLARE @RankCntr tinyint; SET @RankCntr = 1

WHILE @RankCntr <= 13

BEGIN

DECLARE @RankTxt char(1)

IF @RankCntr = 09 SET @RankTxt = 'T'

ELSE IF @RankCntr = 10 SET @RankTxt = 'J'

ELSE IF @RankCntr = 11 SET @RankTxt = 'Q'

ELSE IF @RankCntr = 12 SET @RankTxt = 'K'

ELSE IF @RankCntr = 13 SET @RankTxt = 'A'

ELSE SET @RankTxt = LTRIM(STR(@RankCntr + 1))

DECLARE @PrimeFactor tinyint

SELECT @PrimeFactor = PrimeFactor FROM #Primes WHERE CardId = @CardId

INSERT Dim_Deck VALUES (@PrimeFactor, @SuitTxt, @SuitCntr, @RankTxt,

@RankCntr, UPPER(@RankTxt) + LOWER(LEFT(@SuitTxt, 1)))

SET @CardId = @CardId + 1

SET @RankCntr = @RankCntr + 1

END

SET @SuitCntr = @SuitCntr + 1

END

DROP TABLE #Primes