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 through errors. Let’s understand what we mean by the statement with an example.

Notice that here we have this create table script. This 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. So that’s why they’re using ‘use DEMO’. So this table is going to be created within the DEMO database.

when I run the query the first time, the query is going to execute successfully and the table gets created. So when we refresh “Tables” folder, we can able 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 have that table already. That’s why it says there is already an object named TblDoctors.

so this script is not re-runnable. So how do we make the script re-runnable?

Pretty straightforward. Check for the existence of the table. If the table doesn’t exist, only then try to create the table else print a message stating 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 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

So look at this query.

select * from information_schema.tables where table_name = ‘TblDoctors’ So if that table exists, this is going to return a row.

And then we are parsing the result of this query to function exists().if there is anything return by select * from information_schema.tables where table_name = ‘TblDoctors’ Query, then exists() function is going to return True, otherwise it’s going to 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 the table is successfully created else print a message stating the table already exists in the database.

So 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 OBJECT_ID() function, SQL server built-in function objectID() and pass the name of the object here.

The name of the object is our table name. So if this object already exists in the SQL server database, then it will have an ID, and OBJECT_ID() function is simply going to return that ID. So if there is Id for that object, then we know the table is already there. If it is null, then we know we don’t have a table there, so we’ll go ahead and create that.

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 so if the table already exists, but we want to drop and recreate it.

This is especially true if we are building some test databases. so how do we drop and recreate the tables again, 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 a table and if the table doesn’t exist, you will get an error. So that’s why when dropping a table. Check if the table already exists.

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, so when I execute the query, it’s going to drop the table and then recreate that, no matter how many times we execute the query, it’s going to drop the table, 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’s going to run without any problem. If that column doesn’t exist.

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 column DOB doesn’t exist on the table TblDoctors only then try to add that column.

And notice that here I have another condition table schema. Again, this is very important, especially if your SQL Server has multiple schemas.

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

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

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

The post How to write Re-runnable SQL server query appeared first on Software Development | Programming Tutorials.