April 30, 2012 at 4:18 pm
Hi All,
I need your advice regarding taking snapshot of a table. I have a table named 'Balances'. I need to take the snapshot of this table('Balances') till may 7 2012 data, I need to use this snapshot table to create a report. and again on june 8 2012 i need to take another snapshot of this table and use it to create june report. I want to confirm with you that "CAN WE TAKE THE SNAPSHOT OF A SINGLE TABLE IN SQL SERVER??" I know we can take the snapshot of entire database but not a single table!! Correct me if i am wrong!!
Thanks for your time!!
Raj Vardhan
April 30, 2012 at 4:23 pm
As far as I'm aware, you cannot take a table snapshot (but there are rather talented people on here who may prove me wrong). If I were you I'd pull the info into a datamart using ssis to extract the data from the source table and report from that. HTH
I'm a DBA.
I'm not paid to solve problems. I'm paid to prevent them.
April 30, 2012 at 4:31 pm
Well, no, not really.
You can either reconstruct the data from an auditing structure which will hold all deltas, or you can set up a process (say, that runs overnight) that will copy the data from the table into another table with the date of the copy.
Be forewarned, the second option is a HUGE data-sink.
What is the end goal you are trying to accomplish? I realize you want daily point in time looks at the data, but what's the final intent? That will help drive some of the solutions to what you're looking for.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 30, 2012 at 4:40 pm
Thanks Craig for your response. Our goal is to generate a report by using the snapshot table. we are creating a forecast comparison report which will compare the forecasts given by salesperson each month and we want to show the variance of forecast of months in a single report.
April 30, 2012 at 4:42 pm
Thanks andrew!! Now it is confirmed that we cannot explicitly take the snapshot of a sql table. I will try your method by taking the snapshot data into a data mart....
April 30, 2012 at 5:37 pm
tinnusqlserver (4/30/2012)
Thanks Craig for your response. Our goal is to generate a report by using the snapshot table. we are creating a forecast comparison report which will compare the forecasts given by salesperson each month and we want to show the variance of forecast of months in a single report.
Ah, I see. Yeah, the easiest method is as Andrew suggested then and using a datamart for this, which I see you're going to attempt to implement. Good luck. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply