How To Write Re-Runnable SQL Server Query

In this post, we will discuss writing a re-runnable SQL Server query. So, what is a re-runnable SQL query?

A re-runnable SQL query is a SQL script that, when run more than once, will not throw errors. Let’s understand what we mean by this statement with an example.

Notice that here we have this create table script. This script is going to create the table TblDoctors.

USE Demo
CREATE TABLE [dbo].[TblDoctors](
[Id] [int] IDENTITY(1,1) NOT NULL,
[DoctorName] [nvarchar](max) NOT NULL,
[Gender] [nvarchar](50) NULL,
[SpecializationId] [int] NULL,
CONSTRAINT [PK_TblStudent] PRIMARY KEY CLUSTERED
(
[Id] ASC
)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

I want this table to be created in the DEMO database. That's why we're using 'USE DEMO'. So, this table is going to be created within the DEMO database.

When I run the query for the first time, the query is going to execute successfully and the table gets created. So, when we refresh the 'Tables' folder, we will be able to see the table."

Then, if I try to run the query once again, what’s going to happen? We get an error. Why?

Because we already have that table. That’s why it says there is already an object named TblDoctors.

So, this script is not re-runnable. How do we make the script re-runnable? It's pretty straightforward. Check for the existence of the table. If the table doesn’t exist, only then try to create the table; otherwise, print a message stating that the table already exists. 

So, obviously, the next question is how do we check for the existence of a table? In a previous post, we discussed using the INFORMATION_SCHEMA.TABLES view to list all the tables in a SQL Server database.

So we are going to make use of that view to check for the existence of the table. look at the below query.

USE Demo
If not exists (select * from information_schema.tables where table_name = 'TblDoctors')
Begin
CREATE TABLE [dbo].[TblDoctors](
[Id] [int] IDENTITY(1,1) NOT NULL,
[DoctorName] [nvarchar](max) NOT NULL,
[Gender] [nvarchar](50) NULL,
[SpecializationId] [int] NULL,
CONSTRAINT [PK_TblStudent] PRIMARY KEY CLUSTERED
(
[Id] ASC
)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Print 'Table TblDoctors successfully created in the database'
End
Else
Begin
Print 'Table TblDoctors already exists in the database'
End

Select * from information_schema.tables where table_name = ‘TblDoctors’. If that table exists, this query will return a row.

Then, we are parsing the result of this query to the function exists(). If there is anything returned by the query 'Select * from information_schema.tables where table_name = ‘TblDoctors’', then the exists() function will return True; otherwise, it will return false.

So, basically, look at this expression. If the table does not exist, then go ahead and create the table, print a message stating that the table is successfully created; otherwise, print a message stating that the table already exists in the database.

So, the above script is re-runnable. To check for the existence of the table, we have used information_schema.tables.

There is another way to check for the existence of the table. You can simply use the OBJECT_ID() function, a SQL Server built-in function. You pass the name of the object to OBJECT_ID(). So, if this object already exists in the SQL Server database, then it will have an ID, and the OBJECT_ID() function will simply return that ID. If there is no ID for that object, then we know the table is not there, so we’ll go ahead and create it.

USE Demo
IF OBJECT_ID('TblDoctors') IS NULL
Begin
CREATE TABLE [dbo].[TblDoctors](
[Id] [int] IDENTITY(1,1) NOT NULL,
[DoctorName] [nvarchar](max) NOT NULL,
[Gender] [nvarchar](50) NULL,
[SpecializationId] [int] NULL,
CONSTRAINT [PK_TblStudent] PRIMARY KEY CLUSTERED
(
[Id] ASC
)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Print 'Table TblDoctors successfully created in the database'
End
Else
Begin
Print 'Table TblDoctors already exists in the database'
End

All right, now, depending on what we want to achieve, sometimes, we may want to drop and recreate the table. This is especially true if we are building some test databases. So, how do we drop and recreate the tables again?

First, we check for the existence of the table, and then only drop the table. Because if you don’t check for the existence of the table and try to drop it, and if the table doesn’t exist, you will get an error. So, that’s why it's important to check if the table already exists before dropping it.

And again, to check for the existence of the table, you can either use INFORMATION_SCHEMA.TABLES View or this OBJECT_ID() function.

USE Demo
IF OBJECT_ID('TblDoctors') IS NULL
Begin
CREATE TABLE [dbo].[TblDoctors](
[Id] [int] IDENTITY(1,1) NOT NULL,
[DoctorName] [nvarchar](max) NOT NULL,
[Gender] [nvarchar](50) NULL,
[SpecializationId] [int] NULL,
CONSTRAINT [PK_TblStudent] PRIMARY KEY CLUSTERED
(
[Id] ASC
)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Print 'Table TblDoctors successfully created in the database'
End
Else
Begin
Print 'Table TblDoctors already exists in the database'
End

So, the table is already there. When I execute the query, it’s going to drop the table and then recreate it. No matter how many times we execute the query, it’s going to drop the table and recreate it.

Let’s look at another example. Now, the below SQL script is not re-runnable. Look at what the script is trying to do. It’s trying to add a column, DOB, to table TblDoctors. When we run the script the first time, it runs without any problem if that column doesn’t exist.

However, if I run it again, then it’s going to throw an error because a table cannot have two columns with the same name. So the script is not re-runnable.

Use [Demo]
ALTER TABLE TblDoctors
ADD DOB DateTime

So how do we make the script re-runnable?

Obviously, check for the existence of the column. So how do you check for the existence of the column? Again, you can use INFORMATION_SCHEMA.

Use [Demo]
if not exists(Select * from INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME='DOB' and
TABLE_NAME = 'TblDoctors' and TABLE_SCHEMA='dbo')
Begin
ALTER TABLE TblDoctors
ADD DOB datetime
End
Else
BEgin
Print 'Column DOB already exists in the table'
End

So, if the column DOB doesn’t exist in the table TblDoctors, only then try to add that column.

Also, notice that here I have added another condition for the table schema. This is very important, especially if your SQL Server has multiple schemas.

In multiple schemas, you can have the same table name and the same column name.

So, obviously, you don’t want to be altering a table that is present in a different schema. That's why it’s very important to check the schema as well.

Just in case your SQL Server has multiple schemas, if that’s not the case, it’s enough to check just the table name and the column name. Again, this SQL script is re-runnable, no matter how many times you run it. It's not going to throw an error.