I have a single-table database I inherited and migrated to SQL Server, and then I normalized it by creating, linking, and filling a whole bunch of lookup-type tables that represented items in the main table. I now want to replace those items in the original table with their foreign keys. Am I stuck writing a bunch of queries or UDF's and then a giant INSERT statement to accomplish this, or is there a tool I can use to point at the various fields and have it handle the grunt work for me?
+2
A:
Redgate SQL Refactor comes with a 14 day evaluation period and has a "Split Table" refactoring which sounds like it might do what you need?
The feature is described thus:
Split Table splits a table into two tables, and automatically rewrites the referencing stored procedures, views, and so on. You can also use this refactoring to introduce referential integrity tables. You can select this feature from the context menu in Management Studio’s Object Explorer.
Martin Smith
2010-08-29 00:27:11
I actually own SQL Refactor but I've only ever used it to refactor scripts I write or inherit (which it does very nicely). Geez, I'll have to go back and look at that. Thanks for the heads-up.
Mike at KBS
2010-08-30 01:43:27
I went ahead and played with it. It does, indeed, have a function for doing something like this, but it really just creates the secondary table with SELECT DISTINCT off the primary, then assigns a foreign key constraint to the primary table's field, pointing at the new secondary table. I want to switch to integer-keyed secondary tables, replacing the field in the primary with a new integer ID field and eliminating the original varchar field. I cannot see how to make that happen with SQL Refactor. But I learned something new about a product I own, so I thank you for that!
Mike at KBS
2010-08-30 06:20:15
+1
A:
I have had similar experiences. I once inherited a fairly large database that required serious overhaul to the schema before I would look at it without scorn.
Because the upgrade was fairly significant, I designed an SSIS package to migrate data from the old schema to the new. Lookup activities were helpful to map old text values to the new keys. I kept a script of my old schema and data handy and would repeatedly restore the database in a sandbox and re-migrate until I could satisfy the powers-that-be that the migration was reliable.
I found there was only a moderate learning curve to getting started with SSIS. If the tool is available to you, I recommend giving it a try.
kbrimington
2010-08-29 00:52:36
I've used SSIS and DTS before, I think it's a great tool. Was hoping for something that required less grunt work, since this kind of thing must be a pretty common scenario.
Mike at KBS
2010-08-30 01:45:06