SQL: Best approach to Select and Insert from multiple tables

Question

What do you guys think would be the best approach here? I have about 30 SQL tables that is basically referenced by a [lookup] table. I have to do an insert in another table [FinalTable] for each row in the [lookup] table. Now the [lookup] table looks something like this.

ID, Zipcode, tableID 
1,  60453,   1
2,  90210,   1
3,  60453,   2
4,  60625,   3
5,  60625,   4
6,  60625,   4
7,  60625,   5

there's a tableID that lets me know which of the 30 tables I need to get data from. and then using the zipcode i do a select on that table and insert whatever data set is returned

Using a Cursor to go through each row of the [lookup] table, what would be the best approach here? in terms of efficiency and the fastest queries. and how would one go about making the table selection dynamically in SQL statements? the [lookup] table has about 1k rows of data and would not grow much in the future. but this whole selecting and inserting process needs to be repeated on a monthly basis. let me know what you guys think. thanks.

All the approaches i have so far. Unfortunately, I have only SQL at my disposal. can't use anything else.

1. putting everything in an if-else statement for selecting the different tables and inserts?
2. using case switch statement?
3. writing 30 stored procs for each table?

i would love to change the design but unfortunately, the 30 disjointed tables comes from a client's DB, basic csv flat files we just uploads. the same zipcodes would appear at multiple tables and we have to do inserts based on which table we are suppose to get data from. sucks. how can i fix this?

Answer 1

Given this database design, I would write 30 queries, one for each table, for instance:

SELECT ...
FROM lookup l JOIN table15 t ON l.zipcode = t.zipcode
WHERE l.tableID = 15

And similarly for each of the other 30 tables. This is probably the simplest and most maintainable solution, and might have the best performance too. Not every task needs to be done in a single query.

I agree with @HLGEM's comment, you should consider redesigning this so you don't have 30 separate tables. This design smells like the Metadata Tribbles antipattern.

Answer 2

I agree fully with the comment from @HLGEM. You're running into issues down the road.

However, her is a SQL statment that may work.

INSERT INTO [FinalTable] ({list of columns for [FinalTable]}
SELECT {List of columns for [FinalTable]}
FROM [lookup]
LEFT OUTER JOIN [Table1] ON [lookup].[Zipcode] = [Table1].[Zipcode] AND [lookup].[tableID] = 1
LEFT OUTER JOIN [Table2] ON [lookup].[Zipcode] = [Table2].[Zipcode] AND [lookup].[tableID] = 2
LEFT OUTER JOIN [Table3] ON [lookup].[Zipcode] = [Table3].[Zipcode] AND [lookup].[tableID] = 3
LEFT OUTER JOIN [Table4] ON [lookup].[Zipcode] = [Table4].[Zipcode] AND [lookup].[tableID] = 4
LEFT OUTER JOIN [Table5] ON [lookup].[Zipcode] = [Table5].[Zipcode] AND [lookup].[tableID] = 5

-- LEFT OUTER JOIN other tables

LEFT OUTER JOIN [Table30] ON [lookup].[Zipcode] = [Table30].[Zipcode] AND [lookup].[tableID] = 30