How to take the snap shot of a table in sql server 2008

Question

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 

Answer 1

Hello Raj,

Indeed, you can't make a "table snapshot" as you can do on database level.

But you can do easily make a copy of your table with a SELECT INTO statement:

SELECT *
INTO myTableCopy
FROM myTable

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 2

Adding to Olaf's note, I would take the SELECT INTO "snapshots" into a History or reporting / DW database:

http://www.sqlusa.com/bestpractices/select-into/

---

Kalman Toth SQL SERVER 2012 & BI TRAINING
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012