Wednesday, February 8, 2012

Recirculating Scripts

I often advise students to put the following lines of code at the top of their SQL Server scripts that build and load databases.

use master
drop
database yourdatabase;
go
create
database yourdatabase;
go
use yourdatabase
--insert your code here to create and load tables

This will first make sure you are in the master database and don't get a database in use error when doing the drop. Then drop the database and recreate it. The GO commands separate the script into batches so if the database doesn't already exist the next batch, the create, will run, even though the drop generated an error. This code works fine but there is a more elegant way.

use master
if db_id('yourdatabase') is not null
drop database yourdatabase;
create database yourdatabase;
use yourdatabase
--insert your code here to create and load the tables

This method tests for the existence of the database first then drops it. Either way is fine. Both make it easy to go in and correct some of the code and then just re-run the script.

---Dan

No comments:

Post a Comment