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.
| Author |
Topic |
|
JeTmAn81
Starting Member
11 Posts |
 Posted - 2010-12-16 : 19:52:47
|
| Hi, there are a few situations in the databases I manage where there will be two tables that are essentially the same, only one is for the main data, and the other is for archived historical data.It's easy enough to transfer over records when something needs to get archived, but one problem I've run into is making sure that everything between the two tables matches up when I alter columns in the main table. If I add a column to the main table, it needs to show up in the archival table as well in order for all information to be transferred.Is there any way of having the archival table update automatically? I've decided to just write a stored procedure that can compare the two tables and alter accordingly, but it occurred to me that there might be a better way of doing this. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
JeTmAn81
Starting Member
11 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-12-17 : 12:05:51
|
| we script all DDL changes - no matter how big, or how small - so that we can easily run them again - if we have to Restore, or to repeat them on QA, Staging, Test and eventually Production.We have an Archive table for 90% of our Main tables, so whenever we change a table we have to remember to change the Archive table too.Our Archive Tables are populated by Triggers, and they use SELECT * (deliberately) in the code so that they fail if we miss a column changeNot very sophisticated! but it does bring any discrepancies to light.I suppose we could have a daily scheduled task that just compared Main and Archive tables to ensure column numbers, and data types, matched, so at worst we would get an alert within 24 hours |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-12-17 : 13:38:24
|
quote:
Originally posted by JeTmAn81
quote:
Originally posted by tkizer
There is no other way to do it. But why don't you just run the same alter scripts that were run on the main table?
In this case, I'm just altering the table through the GUI. It's not a huge operation, so I make lots of adjustments like that on my own. Thanks for the advice.
When you use the GUI, it provides an option to script the commands that it'll use to apply your changes. Select the button and save the script so that you can then run it on your archival table. You'll need to hand edit it so that it changes the archival table.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
JeTmAn81
Starting Member
11 Posts |
Posted - 2010-12-17 : 14:53:14
|
quote:
Originally posted by tkizer
quote:
Originally posted by JeTmAn81
quote:
Originally posted by tkizer
There is no other way to do it. But why don't you just run the same alter scripts that were run on the main table?
In this case, I'm just altering the table through the GUI. It's not a huge operation, so I make lots of adjustments like that on my own. Thanks for the advice.
When you use the GUI, it provides an option to script the commands that it'll use to apply your changes. Select the button and save the script so that you can then run it on your archival table. You'll need to hand edit it so that it changes the archival table.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
Well, I've finished my stored procedure and it can actually compare the two tables and add any new columns as well as changing the data types on existing columns to match the main table. So that works pretty well and all I have to do is execute one command.The only problem is that this seems difficult to do on a server wide basis. I started with the procedure living in the master database, but then when I try to run it on tables which actually exist in another database, it doesn't work because I'm querying the INFORMATION_SCHEMA for that other database, and it doesn't seem possible to access the schema for one database from another. |
|
|
|
|
|
|
|
|
Making main table and archival table match up?