Introduction

The “Select Into” statement is something I use on a regular basis, and something I have always insisted my team use prior to making any large data changes. The scenario  would be “I am about to make a major change to several thousand item records, the change is effecting UETs, and I have ran and re-ran the query in a transaction but there is still a risk of getting it wrong. As such I want a “Get out of Jail” free card – ie a way to restore the data to this point in time without having to restore from a backup tape”.

I have a separate sql database to store this data which operated on a different backup schedule, it’s called “data warehouse” but it is more a dumping ground to keep the main app databases clean

The Code

select * into pbti_datawarehouse.dbo.item_bu_20131025 from item

Considerations

– This will not copy triggers, constraints or similar, just the data
– Don’t do this too often – you may quickly run out of space on your drive, especially with some of our bigger tables!