asp.net core docker Writing re runnable sql server scripts Part 66

Text version of the video
http://csharp-video-tutorials.blogspo...

Slides
http://csharp-video-tutorials.blogspo...

All SQL Server Text Articles
http://csharp-video-tutorials.blogspo...

All SQL Server Slides
http://csharp-video-tutorials.blogspo...

All Dot Net and SQL Server Tutorials in English
https://www.youtube.com/user/kudvenka...

All Dot Net and SQL Server Tutorials in Arabic
https://www.youtube.com/c/KudvenkatAr...

What is a re-runnable sql script?
A re-runnable script is a script, that, when run more than, once will not throw errors.

Let's understand writing re-runnable sql scripts with an example. To create a table tblEmployee in Sample database, we will write the following CREATE TABLE sql script.
USE [Sample]
Create table tblEmployee
(
ID int identity primary key,
Name nvarchar(100),
Gender nvarchar(10),
DateOfBirth DateTime
)

When you run this script once, the table tblEmployee gets created without any errors. If you run the script again, you will get an error - There is already an object named 'tblEmployee' in the database.

To make this script re-runnable
1. Check for the existence of the table
2. Create the table if it does not exist
3. Else print a message stating, the table already exists

Use [Sample]
If not exists (select * from information_schema.tables where table_name = 'tblEmployee')
Begin
Create table tblEmployee
(
ID int identity primary key,
Name nvarchar(100),
Gender nvarchar(10),
DateOfBirth DateTime
)
Print 'Table tblEmployee successfully created'
End
Else
Begin
Print 'Table tblEmployee already exists'
End

The above script is re-runnable, and can be run any number of times. If the table is not already created, the script will create the table, else you will get a message stating - The table already exists. You will never get a sql script error.

Sql server built-in function OBJECT_ID(), can also be used to check for the existence of the table
IF OBJECT_ID('tblEmployee') IS NULL
Begin
-- Create Table Script
Print 'Table tblEmployee created'
End
Else
Begin
Print 'Table tblEmployee already exists'
End

Depending on what we are trying to achieve, sometime we may need to drop (if the table already exists) and re-create it. The sql script below, does exactly the same thing.
Use [Sample]
IF OBJECT_ID('tblEmployee') IS NOT NULL
Begin
Drop Table tblEmployee
End
Create table tblEmployee
(
ID int identity primary key,
Name nvarchar(100),
Gender nvarchar(10),
DateOfBirth DateTime
)

Let's look at another example. The following sql script adds column "EmailAddress" to table tblEmployee. This script is not re-runnable because, if the column exists we get a script error.
Use [Sample]
ALTER TABLE tblEmployee
ADD EmailAddress nvarchar(50)

To make this script re-runnable, check for the column existence
Use [Sample]
if not exists(Select * from INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME='EmailAddress' and TABLE_NAME = 'tblEmployee' and TABLE_SCHEMA='dbo')
Begin
ALTER TABLE tblEmployee
ADD EmailAddress nvarchar(50)
End
Else
BEgin
Print 'Column EmailAddress already exists'
End

Col_length() function can also be used to check for the existence of a column
If col_length('tblEmployee','EmailAddress') is not null
Begin
Print 'Column already exists'
End
Else
Begin
Print 'Column does not exist'
End

asp.net core docker Writing re runnable sql server scripts Part 66
208 Likes	208 Dislikes
71,662 views views	524K followers
Education	Upload TimePublished on 26 Jun 2013

Related keywords

wcf vs web api,queries pronunciation,ado.net core,sql server management studio,webkinz,webadvisor,craigslist nj,wcf one piece,asp.net core 3,ajax players,queries spanish to english,weber grill parts,webtoon,tutorials by hugo,csharp corner,mvc design pattern,asp.net machine account,servers for minecraft,asp.net core dependency injection,ado.net tutorial,services angular,ajax ontario,asp.net mvc tutorial,asp.net cos'è,csharp assembly,tutorialspoint python,sql join,services briefcase,asp.net core web api,ajax jquery,wccftech,craigslist ny,asp.net zero,csharp foreach,server memes,sql date format,services online,chase,serverless architecture,server resume,wcf c#,server books,tutorialspoint javascript,mvcc connect,queries anagram,ado.net mysql,services technologies gps,ajax deadpool,server jobs,cvs,website,mvc tutorial,costco hours,wcf service application,tutorialspoint spring,serverless,wcf soap,sql server connection string,sql server tutorial,queries meme,wcf cat,wcf test client,cool math games,services & training hse colombia sas,servicestack,citibank,asp.net core identity,sql union,ajax parking,sql database,asp.net core logging,mvconnect,asp.net guida,cunyfirst,wcf nba,csharp download,wcfi foundation,csharp online,wcf authentication,tutorials near me,http://asp.net,server 2019,queries definition,ado.net descargar,web of dreams,chernobyl,serverminer,ajax cleaner,ado.net visual studio 2019,webassign,queries meme meaning,ado.net vs entity framework,ado.net visual studio 2017,csharp list,sql like,asp.net mvc,asp.net core tutorial,sqlite,wcfm,queries in excel,ajax roster,mvc architecture,http://ado.net,queries per second,asp.net core mvc,ajax soccer,sql server configuration manager,server hosting,wcfi,ajax dish soap,capital one,server rack,tutorialspoint html,csharp interface,craigslist,sql server download,webroot,tutorialspoint reactjs,ajax request,wcf dragon ball,asp.net core 2.2,tutorialspoint python 3,sql developer,webster,sql server date format,services transmission company sas,sql group by,asp.net core signalr,services manager,mvc framework,queries sql,ajax paving,mvc near me,queries in spanish,tutorialspoint spring boot,mvc map,queries unscramble,csharp online compiler,asp.net download,sql between,ado.net c# pdf,wcf 2019 nba,services tag dell,csharp switch,ado.net ventajas y desventajas,csharpstar,wcf tutorial,queries in access,queries in google sheets,sql server 2016 download,tutorialspoint,ajax meaning,csharp-video-tutorials.blogspot,tutorials dojo,csharp string format,central park 5,ado.net c#,asp.net core github,server status,ajax fc,server jobs nyc,asp.net core swagger,sql formatter,credit karma,services group,sql server 2008,server error in '/' application,services windows,asp.net core 3.0,sql injection,wcf ria services,tutorialspoint c#,calculator,ado.net entity data model,sql insert,tutorialspoint tableau,queries database,services google play apk,sqlyog,asp.net core 3 release date,sql server,server job description,tutorials by a,servicenow,webcam,mvc hours,webmd symptom,csharp array,csharp enum,ajax call,asp.net core 2. guida completa per lo sviluppatore,asp.net core,server pro,server status ffxiv,webcrims,cheap flights,asp.net core hosting,services sas,tutorialspoint java,tutorialspoint java compiler,webmd,sql server 2014 download,csharp to json,college football,ado.net dataset,csharp dictionary,cnn,website builder,tutorialspoint sql,asp.net web api,server side rendering,weber grills,sql server 2014,sql server 2016,sql server 2017,queries translate,mvc nj,sql server 2012,tutorialspoint spark,ado.net oracle,asp.net core download,csharp to vb.net,sql server 2019,queries dog,webster bank,webstaurant,tutorialsystems,ajax post,services fms publish announcement,services.msc no abre,ajax jersey,csharp operator,asp.net core razor pages,server duties,asp.net core environment variables,csharp random,century 21,queries synonym,sql server data tools,services consultores,services consulting,mvcsd,mvcsc,services.msc,ado.net pdf,asp.net core configuration,ajax marvel,sql update,asp.net tutorial,mvc medical,ado.net entity data model visual studio 2019,wcfs international curriculum,mvc2,ado.net entity data model visual studio 2017,chase online,wcf api,costco,server jobs near me,queries meme dog,webex,sql meaning,sql tutorial,sql server express,sql commands,sql server 2008 r2,ado.net entity framework,ajax tavern,sql server versions,tutorialsteacher,ajax javascript,services desk,queries meaning,queries and connections in excel,ajax greek,csharp tutorial,mvc pattern,ado.net sql server,ado.net connection,asp.net identity,mvcu,asp.net core middleware,sql server management studio 2014,wcf web service,mvc webadvisor,web store,mvcc,webmail,mvci,mvctc,sql server management studio 2012,