September 8, 2014 at 3:11 pm
I'm going to do my best to explain this in an understandable fashion.
Table 1 has column A, and column B.
Tables 2, 3, 4, 5, 6, 7, 8 (into the thousands) only have column A.
I need to add column B to all of the other tables.
For this I simply pulled the list of tables from sys.tables, threw it in excel, scripted out a quick concatenation to the tune of "ALTER TABLE 2 ADD B varchar(50)" and auto filled, and that'll just be a simple copy, paste, execute.
Simple enough. There's almost certainly an easier way to do that than my approach, but that's not the main reason I'm posting.
The thousands of other tables all have at least 1 entry in them that matches a unique identifier in Table 1 - for this example that will be column A.
So now I have to go through thousands of tables, and update those tables and set the newly added column B to the same value that exists in table 1 column B, based on a match of column A.
Now, if I haven't made you go cross-eyed yet, and you're still with me....I have once again come up with a spreadsheet that lists out all of the tables individually, and added a concatenation script to update them - but I cannot imagine it is at all idea.
An example of one such script using my scenario above:
UPDATE 2
SET 2.B = 1.B
from 1
where A.1 = B.1
UPDATE 3
SET 3.B = 1.B
from 1
where A.1 = B.1
UPDATE 4
SET 4.B = 1.B
from 1
where A.1 = B.1
So on and so forth.
While I believe this approach would be perfectly fine for a one off, I cannot imagine that it's ideal for as many table updates that I'm looking to do (nearly 5000).
Any help/advice would be greatly appreciated. Thanks guys!
September 8, 2014 at 3:18 pm
The first, most obvious though is "don't do this". Why are you denormalizing one table's data into thousands of other tables? Why not just join to the original table to get the value?
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
September 8, 2014 at 3:38 pm
Scott the best way I can explain it is that each of these separate tables are used for "campaigns" - not all of which are active. Why that wasn't just originally implemented into the big table, I don't know. So the data in each of these smaller tables are used by the front end of an application.
I understand that it's very far from ideal, but unfortunately fixing it the way that it should be currently isn't really an option.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply