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.
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.
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.
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.
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.
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.
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.
Read More Articles
- Linq to SQL Group by and Sum in Select | Linq To SQL Group By and Sum
- How send an HTTP POST request to a server from Excel using VBA?
- What is Difference between where and having clauses in SQL server
- How to Use EXCEPT Operator with an example in Sql
- How to use merge statement in the SQL server
- How to write Re-runnable SQL server query
- How to create Cursor in sql server with example
- How to generate random data in sql server for performance testing
- How to write subquery in select statement in Sql
- How to Log record changes in SQL server in an audit table
- Logon Trigger Example to Restrict Access in Sql Server
- DDL Triggers In Sql Server with Example | Database and Server Scoped Triggers
- How to achieve ACID properties with Example in Sql Server
- What is transaction in sql server with example
- Instead Of Delete Trigger In Sql Server With Example
- Instead Of Update Trigger In Sql Server With Example
- Instead Of Triggers in Sql server With Example
- After Update Trigger in Sql Server With Example
- Triggers in SQL server with real-time example
- C# -Saving a base64 string as an image into a folder on server in Web Api
- what are the advantages and disadvantages of indexes-Sql Server?
- Sql Server- Difference between unique and non unique index
- Temporary Tables in sql server with real time example
- Clustered and Non clustered index in sql server with real example
- How does Recursive CTE works in Sql server?
- How to update table using CTE in sql server
- How to use group by in SQL Server query?
- Sql Server pivot rows to columns
- What is CTE in sql server with example
- SQL Server – -Simple way to transpose rows into columns
- What is Sql Joins? With Realtime Examples
- Difference between Inner Join ,Left Join and Full Join-Sql
- How to get Employee manager hierarchy in Sql ?
- How to create Inline table-valued functions in SQL server
- Scalar User-defined functions In sql server-Step by Step
- [Solved]-How To Update a Table using JOIN in SQL Server?
- Create Stored procedure with Output and Input parameters in SQL
- [Solved]-Best Sql server query with pagination and count
- [Solved]- find records from one table which don’t exist in another Sql
- [Solved]-How to concatenate text from multiple rows into a single text string in SQL server?