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.
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
- Double curly braces on SET operation sql
- Insert performance tuning
- Creating Subtotals - SQL Server 2008
- JDBC Connection pooling for SQL Server: DBCP vs C3P0 vs No Pooling
- Daylight savings time in Sql Server
- What is the max number of parameters that can be passed in stored procedure
- ASP.NET WebAPI and jQuery with large dataset (json)
- Find Contractors that Only Provided Service2 in Each Project
- how to set datetime from java to sql with Date today = new Date();
- SQL - Get the value from the next lowest ID
- SQL Server procedure to display if date is a weekday or weekend
- Sql timeout Exception although max pool size is set to 1000000
- How do I display records containing specific information in SQl
- SQL Join on table with revision dates
- EF and TPT : the column name is specified more than once in the SET clause
- Can't save changes on INSERTed records
- has ms sqlserver data vault feature?
- SqlDependency fires immediately
- SQL Convert each date range into each day row
- Can i block insert into all table using a server trigger for a specified database
- How to make Ranking based on Year and Month?
- Select all rows where a varchar column converts to a decimal
- Which query is more appropriate?
- How can I determine largest bit in a bitwise value
- SQL Server and C# Master / Detail Insert
- SQL query to identify records that are effective during a certain interval
- Security script based on Global Group?
- Java Callablestatement doesn't wait until .execute() ends
- How track all data changes on a database
- SQL Server 2008 Performance Question
- How to transform a MSSQL CTE query to MySQL 5.7?
- How to improve poor performance of EF Core SQL query that sorts on a child collection
- SQL Server Like Query not case sensitive
- Highlight multiple records in a date range
- The parameter PASSWORD cannot be provided for users that cannot authenticate in a database
- SQL Server Query(s) Timeout (both Read/Write) when Report Query is Running
- Can't understand this query
- Schema, Owner for objects in MS SQL
- Best way to archive sql data and show in web application whenever required
- how do you populate sql query result in a textbox or input control like google search dropdown result
- How to Select and then add a full table on top of it?
- Pattern matching on encrypted columns
- LEAD over a Cursor variable
- SQL use of OVER and PARTITION BY
- What are options for database management for cloud computing (chrome os specifically)?
- How to attach .mdf file to .SQLEXPRESS
- More efficient way to write this query - several M:M relationships
- Make columns out of a date range
- How to query for Select, Group, then Join with date constraint
- Can you do a regex in the select statement on SQL Server?
- Programatically connect and refresh tabular data model on SQL Sever Analysis Service
- Delete Foreign Constraint in Symfony
- Does SQL Azure now support newsequentialid
- how to analyze slow queries?
- replication with SQL AZure from Azure VM
- Use bitmask column or several bit type columns?
- Selecting max value from 2nd table in first table results
- Is it possible to change the datatype Date in Sql Server
- Scripting tables from Sql 2008 Mgmt Studio
- How to export SQL queries to Excel where the Excel file does not yet exists?
- Why is VARCHAR slower than CHAR on updating rows?
- sql.Connection is not a constructor
- SQL Server 2012 - find duplicate month (string) but different year
- How to optimize SQL Server Columnstore Alignment
- WordPress with MSSQL
- How to search text in SQL Server ignoring special chars?
- Calculate financial year start and end date based on year entered SQL Server and SSRS
- How to set OUTPUT param for sp_executesql to be a custom table type?
- Insert data from one database to another database if record does not exist
- Concurrency issue in SQL server 2008
- SQL Server Datediff returning overflow error when query has no results
- Insert a data in OLE DB destination
- SQL Server send email when query result is not empty
- Use SQL Server Always Encrypted with SSIS and Azure Key Vault
- In SQL Server, deleting nodes using .modify() XQuery taking 38 minutes to execute
- Add a security layer to our SQL Servers (currently accesible from remote sql management studio)
- SQL Query returns nothing if one field is empty
- SQL Server Group Records Based Another Columns
- Sp_msforeachtable Returns Just First Table In C#
- Finding most queried items in a table in SQL Server
- Month sorting in sql
- SQL Query returning repeating information
- Why am I getting the error message "TOP or FETCH clause contains an invalid value"?
- SQL Server 2008 delete all tables under special schema
- Extracting certain values from XML column
- sql server 2012 - could not find an entry for table or index with partition id yyyxx
- Multiple selection from SQL Table
- Matching Records With Missing Data
- Calling Powershell script from SQL Server trigger
- Where can i find the: 1) cache hits and 2) cache lookup stats in SQL Server
- Transferring MSSQL Stored Procedures to MySQL
- MS SQL last record
- Two Where conditions on same column
- Select data from the same the column that meets two conditions
- Error message when inserting date into table
- Translate postgres crosstab query to TSQL
- Google Apps Script to Microsoft SQL Server clarification of whitelist certain IP ranges in database settings
- Alternative to cursor when applying a list of values to a where clause?
- SQL server alternative of OR in where condition
- Migrating from SQL Server to neo4j , is there something like SSMS to import data into neo4j
- ADO.NET Long running transactions
- Find out the calling stored procedure in SQL Server
- Finding duplicate values in a table where all the columns are not the same
- SQL Query for SCD Type 2
- sql select top X to X+N
- SQL Server conditional full text index creation
- SQL Server stored procedure to add days to a date
- Can I set defaults value for two columns in a table sql in one command?
- INSERT Pandas Dataframe to SQL-Server using Python - Jupyter Notebook
- Mac OS X + cakephp + SqlServer
- NodeJs Express app.get that handles both query and params
- Accesing Azure MSSQL server from Ubuntu and PHP
- Display Featured Records First Using JOIN/UNION
- SQL query to retrieve value or catch all
- How to show values that has a suitable pair in sql server
- Getting DateTimeOffset value from SQL 2008 to C#
- Dynamically select distinct current and previous columns from a sql table
- How to store data from a richtexteditor to accommodate searches?
- Rename Tool for SQL Server
- SQL Server - Create temp table if doesn't exist
- Is there a translator library from Access syntax SQL to T-SQL available for C#?
- Cannot convert char to datetime in windows counters table
- SQL Server insert statement using INSERTED to get new ID and existing ID
- SQLClient What error numbers are related to SQL Server Connection errors so I can retry
- Visual Studio Database Project Script Domain Name
- Update multiple values in a table in a single query
- Regex number and dashes
- How do I restore a single table from a SQL Server 2005 backup?
- Insert into SQL Server table using C#
- SQL Server: store more integers in a single value
- TSQL logging inside transaction
- Find broken objects in SQL Server
- Dealing with UPDATEing from another table when the JOIN is multiple columns
- Return column based on calculated value of other columns
- sub query in sql error
- PDO::quote() for SQL Server mis-quotes strings containing ASCII NUL
- Determine 'this week' in T-SQL
- MSSQL SELECT -get one result per group
- Set database name dynamically in SQL Server stored procedure?
- VS2015 - SSIS custom component - bespoke form not showing, instead get Advanced Editor
- TSQL Temporary Procedure: There is already an object named
- Wrong count result
- Querying tables: complex join
- String to DateTime in C# to save in SQL server
- SQL Server data grouping
- Extract string from specific character in sql
- SQL Server create bitset larger than POWER(2,30)
- Search row for invalid characters in SQL
- SQL Server Difference (opposite of intersect)
- Do I need to use this many indexes in my SQL Server 2008 database?