July 22, 2009 at 5:05 pm
Hello everyone.
I have a bit of a challenge I am attempting to resolve but keep coming up at dead ends.
I have an Invoice table that once the books close for the month I create a snapshot of that months data and store it in an archive (works fine). Then, I run several update statements to apply certain rules to give commissions to our sales reps (also works fine).
The problem is, there are 28 update statements. I am working on making this process a bit more automated, but I have to go into the stored procedure and do a find replace each month on the table name and replace them with the new months table name (i.e Invoice_2009_06 goes to Invoice_2009_07).
I created an indexed view which seems to work fine, but I would like it to be based on the new table. I could create an SSIS to update the view, or possibly a stored procedure, but I want to see if there is a more basic way to do this. My main concern is adding too many esoteric processes and having to document it all. I like to keep as much as possible straightforward and simple 🙂
Link to my blog http://notyelf.com/
July 22, 2009 at 5:31 pm
shannonjk (7/22/2009)
Hello everyone.I have a bit of a challenge I am attempting to resolve but keep coming up at dead ends.
I have an Invoice table that once the books close for the month I create a snapshot of that months data and store it in an archive (works fine). Then, I run several update statements to apply certain rules to give commissions to our sales reps (also works fine).
The problem is, there are 28 update statements. I am working on making this process a bit more automated, but I have to go into the stored procedure and do a find replace each month on the table name and replace them with the new months table name (i.e Invoice_2009_06 goes to Invoice_2009_07).
I created an indexed view which seems to work fine, but I would like it to be based on the new table. I could create an SSIS to update the view, or possibly a stored procedure, but I want to see if there is a more basic way to do this. My main concern is adding too many esoteric processes and having to document it all. I like to keep as much as possible straightforward and simple 🙂
This sounds like a job for CREATE SYNONYM.
Once you create the table, drop the synonym, and then dynamically build it, pointing to the new table.
Then, all of the updates (and the view) can point to the synonym.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 22, 2009 at 6:36 pm
Brilliant,
Works like a charm. I have a stored procedure already in place that pre loads all of this. I just simply added a drop/create synonym statement to accommodate the new table.
Link to my blog http://notyelf.com/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply