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
- C# + SQL Server ExecuteScalar() not returning last inserted id
- T-SQL AVG of multiple columns in a row
- SQL Show Number of Months and Years Between Two Dates
- What's the difference between selecting from two tables and using an inner join in MSSQL?
- SQL - Returning all rows even if count is zero for item
- Azure SQL database creation issue from dacpac
- Is there a way to retrieve inserted identity as well as some values from the query in an INSERT SELECT?
- Is It possible to get JSON as an out parameters using sp_executesql
- Get new ID before insert during transaction in .NET
- How to find duplicate entries with different values in one column SQL Server 2008 R2
- Passing PHP String as SQL Command and Preserving Line Breaks
- PARTITION BY with date between 2 date
- Why does a temp table work but not a permanent table?
- SQL Select Query Question with Multiple Nested Condition
- Get column data type from table and some condition
- Trouble deleting duplicate records using partition and rank() in sql server
- SQL - safely downcast BIGINT to INT
- EF insert in link table creates entries in one of the parent tables
- What is faster: SUM over NULL or over 0?
- Retrieve detail rows of a group based on grand total
- Partial search with LIKE and JSON object
- Converting Text File to images for preview in .NET
- Locking a SQL Server table to prevent inserts
- Using LAG for SUM in SQL Server
- Drop default constraint on a column in TSQL
- Microsoft SQL Server Analysis Services OLAP cube
- What is the MySQL equivalent of SQL Server's UPDATE() trigger function?
- How do I insert the contents of a subselect in MS SQL Server?
- Is it possible to apply default filter on a table in SQL Server?
- Inno Setup - "An attempt was made to load a program with an incorrect format" when trying to install NetFx3 feature
- Sql types and encrypted size
- SQL Server - weird CASE bahavior - Simple Case vs Searched Case
- SQL PIVOT on Date Range
- execute stored procedure using sqlsrv in php 7+
- Connect to an SQL 2008 server in PHP (Windows/Linux)
- Sql Server one-to-many relationship in same table
- Connecting to SQL Server Analysis with a windows user
- Get results from multiple tables after grouping using Linq-to-SQL
- Is writing to a SQL database slower than reading?
- Encapsulate complexity in table variables is a best practice?
- SQL Server deadlock when using PreparedStatements
- Why can't I Execute SQL when I generate database from entity framework model?
- Velocity caching and IS
- How to exclude rows with duplicate columns?
- SQL Server : update every 5 records with serial number
- How can I group rows into columns with multiple values?
- Trouble with contradicting where clause
- Script to determine if a stored procedure is readonly or read-write
- Persist variables in SQL commands using C# and one connection
- Cannot open database in windows service
- "Invalid column name" error when using data migration scripts
- SQL Sever 2008 R2 - Transforming a table with xml list columns to individual rows in new table
- SQL Server Join two tables with where condition on both tables
- How to write UPDATE from XML with Table Alias in SQL Server 2008 already have a 'From' statement
- Return all words starting with a character in a column
- Get star employee record from attendance according to each Month
- Numeric comparisons on string column data
- Ordering SQL query by hierarchy and it's random code
- How to select results when using 2 paired arrays as inputs
- Sql Server strict convert from varchar to datetime
- Is IN and NOT IN mutually Exclusive?
- Increasing column length in a large table
- Can't use LONG data type
- Most recurring weekday of a datetime2 fiel on a Table
- SQL Server query Where string has ':' colon in it returns not found
- Find Duplicates in table
- How to find what type of exception occur from SQL Server Try Catch while executing stored procedure
- How to order result by values IN() operator in SQL Server
- How to prevent NHibernate ISession to change connection after every query execution?
- about sql server query
- How to export SQL Server 2005 rows to excel? Everytime I do it I get errors
- Make multiple columns into one column in sql
- No sum() with null values
- Cannot resolve the collation conflict between "Cyrillic_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation
- non-english character displaylike this?
- Deadlock graph in SQL server profiler shows mutual lock on the same clustered key
- sql server query to replace variables with values recursively
- Login failed for user 'NT AUTHORITY\SYSTEM'
- group by query in sql server returns duplicates
- Using Transaction in ADO.net SQL
- Query for Joining three tables with row value as column header
- Sort temp table in SQL Server
- Visual Studio publish a Database project
- Query results as comma-delimited string without using PIVOT?
- Multiple queries with Entity Framework in C#
- Missing index explanation
- Deploy database project to SQL Server 2008 Express
- C#, Dapper, SQL Server and Connection Pooling
- Open source or low cost "log shipping" program
- Python pyodbc cursor vs database cursor
- SQL table variable
- Dynamic SQL JOIN instead of UNION
- Package Validation Error
- Replace “<” and “>” with “<” and “>” in sql server
- How to convert SQL Server image type to normal text in c#?
- SQL where last n records for each id meet a certain criteria
- Create table as value from select statement
- PHP5 - SQLSRV too slow
- AcquireConnection method call to the connection manager <Excel Connection Manager> failed with error code 0xC0202009
- How do I change my asp.net membership database from express to standard sql?
- how to pivot a simple joined data set?
- Loop through XML using SQL
- In class design are nested classes always better?
- A way to get averages over a period of days
- SSIS 2012 Error Output from Ole DB Command
- help, stored procedures and cursors
- ASP.NET Core MVC: connecting to existing database using SQL Server authentication
- Installing MSSQL server express using docker with full text search support
- How to Update and sync a Database tables at exactly same time?
- Find the exact error record
- Pulling SQL Server service info in PowerShell, Escaping $ sign powershell
- The column name is not valid - error
- Secondary Replica SQL Server(Availability Groups )
- What are the issues of having multiple database collations in single SQL Server instance?
- T-SQL code snippet with time of insertion
- mysql count and then group by that count
- Fill month gaps based on multiple columns and aggregate data
- Is IDENTITY_INSERT session-specific?
- Extract string from specific character in sql
- Why SQL Server Management Studio 2005 showing Version 2012 on running 'select @@version'
- replace sub string in SQL Server
- Same ID with multiple records to single row
- how to count returning zero? ms sql
- SQL Server dynamic pivot returning null value when none exist in the data
- How to access external DLL's static methods that incorporate output params in SQL Server
- Slow performance importing MS Access database into SQL Server
- Querying a many to many table
- How to ensure referential integrity which relies on a value
- SQL: How do I group these survey answers?
- How to display database image (bytes[]) in mvc WebGrid
- SQL - Why values not selected?
- Filtering unicode column in Sql server 2005 - to much data match
- SQL Server 2005: Deny Access to sp_prepexec
- Use SQL Server check constraints or Application logic to keep Referential Integrity on User Id?
- SQL Server Update Using Multiple Tables
- SQL server-declare local variable: "there is already an object named '' in the database"
- Which inserts faster, XML field or Varchar(max) field?
- Unable to connect to SQl Server 2008 Express / SQl Server 2005 (Local) - Error 40 (Server was not found / not accessable)
- How can I ORDER BY in traditional 'time' increments, a value string, ex: "10:00 AM", from a database entry that is NOT in date() format?
- How to update only days of a date in SQL Server
- SQL Server UNDO
- Calculate Distinct count when joining two tables
- SQL Server insert multiple rows efficiently
- Which version of SQL Server I would need to purchase in order to be able to legally include in a web app I am going to package?
- Could someone please provide a walkthrough for deploying a SQL Server / VC# app?
- How to express a range over multiple columns with hierarchic relation?
- SQL Conversion failed when converting the varchar value 'AJH 123' to data type int
- How do I write paging/limits into a SQL query for 2008 R2?
- Create a range from consecutive zip codes
- Optimising a table to search for geographic locations