Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2005 Forums  SSIS and Import/Export (2005)  Creating Dynamic Master Reference Table

Author  Topic  smbarney Starting Member 1 Post Posted - 2009-04-30 : 10:40:55 I have a very large data base. There are four base tables: Invid, Address, History, and FieldCase. Each day, I receive flat text files that provide updates to the base tables, adding a couple of hundred thousand new rows of data to each base table (I have no control over how I get the data). What I need to do is create a series of lookup tables that will make it possible to search the database more effectively and help me better manage the indexes. The only common field each base table has is a "RectID" field. A RectID can appear many times in each table. I would like to create a master “lkup_RectID” table that references the RectID number in each table. I can easily create a select distinct statement for each table, combine the results into a master RectID table (lkup_RectID) and then create a Foreign Key relationship with each of the base tables. The problem I have is that new RectID numbers are created every day. While importing the daily update files, I need to be able to maintain the Foreign Key relationship with lkup_RectID table and, if a new RectID is found, add it the lkup_RectID. How can I do this without dropping and recreating the lkup_RectID each time? Thanks.