×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Sql Server - Setup 30 Lookup Tables

Sql Server - Setup 30 Lookup Tables

Sql Server - Setup 30 Lookup Tables

(OP)
Timely insight needed.

Currently using TSQL to validate data by initially bulk inserting from a text file (containing approximately 30 lookup fields) to a temporary staging table (Staging table 1). Then, inserting into staging table 2 and validating for duplicate records and then finally inserting from staging table 2 into the final destination table.

It appears that to include a validation on a lookup field using a CASE statement to determine if the value is valid or not is not efficient if the number of lookup values is rather exhaustive - say, for all 50 states in the United States.

It appears that the preferred option is to use foreign keys insead of a check constraint and create the numerous look up tables. Creating the lookup tables with a auto id field and specifying the primary key as the id field. But, then specifying a UNIQUE constraint on the field "Code" within the CREATE TABLE section when I setup each lookup table.

CODE

[Code] [varchar] (15) NOT NULL UNIQUE, 

Considering that I have approximately 30 lookup fields that is within the text file that I have to load every month, is there another method to use that does not involve the setup of foreign keys?

It appears that I have to add 30 foreign key constraints - one for each lookup table and also add multiple inner joins when building queries. Is this correct?

Further, it appears that one has to know the id of the associated value within the lookup table. For example, within the WHERE section of option 2 query below, I have;

CODE

where EL.EquipCodeCode_Id in (6,10)
and SL.StateCode_Id = 1 

Also, I am currently reviewing the implementation of clustered primary keys, indexes, etc. but not familiar with any advantages that these will provide or how to setup.

Any insight regarding the setup of approximately 30 lookup tables is appreciated.


The overall objective is to initially setup the database as efficiently as possible to ensure that the setup of extensive queries/reports involving the lookup fields will be seamless.


CODE

Use CapitalEquipment



IF OBJECT_ID('tempdb..#Bureau1') IS NOT NULL
BEGIN
	DROP TABLE #Bureau1
END
GO
--select * from #Bureau1
CREATE TABLE [#Bureau1] (		
		[Equipment_Code]   		[varchar] (60)  NULL,
		[Account_Number]   		[varchar] (60)  NULL,
		[Sequence_Number]   		[varchar] (60)  NULL,
		[Equipment_Condition_Code]   	[varchar] (60)  NULL,
		[State_Code]   			[varchar] (60)  NULL,
		[Tracking_Number]   		[varchar] (60)  NULL,
		[Sale_Date] 			[varchar] (60) 	NULL,
		[Sale_Amount]  			[varchar] (60) 	NULL,
		[Purchase_Zipcode]   		[varchar] (70)  NULL
)	
GO


BULK INSERT #Bureau1
   	FROM 'C:\Data\Equip_2016.txt'
	
   WITH
      (
         FIELDTERMINATOR = '\,',
		 ROWTERMINATOR = '\n',
		 FIRSTROW = 2
      );
GO



IF OBJECT_ID('CapitalEquipment..Equipment_2016') IS NOT NULL
BEGIN
	DROP TABLE Equipment_2016
END
GO

CREATE TABLE [Equipment_2016](		
		[Equipment_Code]   		[varchar] (5)  NOT NULL,
		[Account_Number]   		[varchar] (45)  NOT NULL,
		[Sequence_Number] 		int NOT NULL,                   
		[Equipment_Condition_Code]   	[varchar] (1)  NULL,
		[State_Code]   			[varchar] (6)  NULL,
		[Tracking_Number]   		[varchar] (15)  NULL,
		[Sale_Date] 			date  NULL,                      
		[Sale_Amount]   		int  NULL,
		[Purchase_Zipcode] 		[varchar] (9)  NULL,
		Constraint PK_Equipment_2016 Primary Key ([Equipment_Code], [Account_Number], [Sequence_Number])
	
		Constraint FK_Equipment_2016 FOREIGN KEY ([State_Code]) REFERENCES Lookup_State_Code ([Code]),
		Constraint FK_Equipment_2016 FOREIGN KEY ([Equipment_Condition_Code]) REFERENCES Lookup_Equipment_Condition_Code ([Code]),
		.
		.
		.
		WITH (IGNORE_DUP_KEY = ON)


)
GO 


CODE

/****************************
CREATE LOOKUP TABLES
*****************************/
 
IF OBJECT_ID('CapitalEquipment..Lookup_State_Code') IS NOT NULL
BEGIN
	DROP TABLE Lookup_State_Code
END
GO

CREATE TABLE [Lookup_State_Code](		
		StateCode_Id int identity not null primary key (StateCode_Id), 
		[Code] [varchar] (15) NOT NULL UNIQUE,
		[Desc] [varchar] (100) NULL
		
	)	




IF OBJECT_ID('CapitalEquipment..Lookup_Equipment_Condition_Code') IS NOT NULL
BEGIN
	DROP TABLE Lookup_Equipment_Condition_Code
END
GO

CREATE TABLE [Lookup_Equipment_Condition_Code](		
		EquipCodeCode_Id int identity not null primary key (EquipCodeCode_Id), 
		[Code] [varchar] (15) NOT NULL UNIQUE,
		[Desc] [varchar] (100) NULL
		
	)	


/****************************
DATA WITHIN LOOKUP TABLES
*****************************/


Equipment Condition Lookup Table

Value	State		
000	NONE
001	Not Known
CCC	Not Known
AVG	AVERAGE
DMG	DAMAGED
EXC	EXCELLENT
FAI	FAIR
GOO	GOOD
POO	POOR
VGO	VERY GOOD



State Code Lookup Table

Value	State		
AL	Alaska
AZ	Arizona
.
.
.
CA	California
.
.
. 


CODE

/******************************************************************
QUERY OBJECTIVE

Extract equipment that are in excellent and very good condition that are located in Alaska
*******************************************************************/


Option 1 - Without Lookup Tables

Select Account_Number, Equipment_Condition_Code, State_Code
from Equipment_2016
where Equipment_Condition_Code in ('EXC','VGO')
and State_Code = 'CA'

Option 2 - With Lookup Tables

Select Account_Number, Equipment_Condition_Code, State_Code
from Equipment_2016 EQ
INNER JOIN STATE_LOOKUP SL
ON EQ.State_Code = SL.Code
INNER JOIN EQUIPMENT_CODE_LOOKUP EL
ON EL.CODE = EQ.Equipment_Condition_Code
where EL.EquipCodeCode_Id in (6,10)
and SL.StateCode_Id = 1 







RE: Sql Server - Setup 30 Lookup Tables

You don't need to use an auto id field for the primary key. You can use your code field just as well. What I mean is, let your code field be the primary key. Since it's the primary key, it will still need to be unique.

ID fields are ideal for primary keys because the primary key is usually created as a clustered index, and the physical sorting of the table is based on the clustered index, and it's faster to sort integers than it is to sort varchars. However, I suspect that each lookup table is probably not all that large, so performance wise, you probably won't notice. Anyway... give it a shot to see how it works for you.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close