Generate Insert Scripts of Existing Rows with "if Exists" Conditions

• Download source - 2.9 KB

Introduction

Data transfer is one of the most frequent tasks in application programming. If you are in application programming and have supported production environment, you will easily understand sending insert script to run in production, be it configuration entries or data correction script.

If you are sending data script to production, you need to be extra cautious. Even one incorrect data value can cause complete chaos. And, that is why it takes time to build these scripts.

This article will provide one utility stored procedure to generate insert with lot of flexibility. You will be able to generate script:

1. From any table you want
2. On the basis of conditions
3. With “If Exists” clause based on columns you provide
4. Including or excluding identity column values
5. Excluding column based on your input

Background

Like you, I had also gone through all this pain. Although there are some free options available like - SQL Server “Generate Script” tool as well as some published articles/options over the internet. However, I needed some tool/utility to get more command over generated inserts. Basically, I needed the following additions to already existing tools/utilities:

1. I should be able to generate scripts on the base of any conditions like we can select any number of rows based on conditions.
2. I should be able to generate scripts with “If Exists” Clause as I am not sure if data already exists in prod.
3. I may like to exclude excluding identity column values.
4. I may like to exclude some columns (for example – System columns may be defaulted)

Using the Code

Note: For all our examples, we have used “Northwind” database.

To use stored procedure (usp_CreateInserts) for generating inserts, follow these steps:

1. Download attachment from this article.
2. Open SSMS and run usp_CreateInserts.SQL in your DB.

Example of generating all inserts from “Orders” table:

EXEC [dbo].[usp_CreateInserts] @TableName = 'Orders', @FromAndWhere = 'FROM Orders'

/* Query will give result in this format

IF NOT EXISTS(SELECT TOP 1 1 FROM Orders WHERE 1=1)
    BEGIN 
    INSERT INTO [Orders] ([OrderID],[CustomerID],[EmployeeID],[OrderDate],[RequiredDate],[ShippedDate],_
                [ShipVia],[Freight],[ShipName],[ShipAddress],[ShipCity],[ShipRegion],[ShipPostalCode],_
                [ShipCountry])VALUES(10248,'VINET',5,'Jul  4 1996 12:00:00:000AM',_
                'Aug  1 1996 12:00:00:000AM','Jul 16 1996 12:00:00:000AM',3,32.3800,_
                'Vins et alcools Chevalier','59 rue de l''Abbaye','Reims',NULL,'51100','France') 
    END
IF NOT EXISTS(SELECT TOP 1 1 FROM Orders WHERE 1=1)
    BEGIN 
    INSERT INTO [Orders] ([OrderID],[CustomerID],[EmployeeID],[OrderDate],[RequiredDate],[ShippedDate],_
                [ShipVia],[Freight],[ShipName],[ShipAddress],[ShipCity],[ShipRegion],[ShipPostalCode],_
                [ShipCountry])VALUES(10249,'TOMSP',6,'Jul  5 1996 12:00:00:000AM',_
                'Aug 16 1996 12:00:00:000AM','Jul 10 1996 12:00:00:000AM',1,11.6100,_
                'Toms Spezialitäten','Luisenstr. 48','Münster',NULL,'44087','Germany') 
    END

    .
    .
    .

*/

Example of generating all inserts from “Orders” table on basis of some condition (Where Clause):

EXEC [dbo].[usp_CreateInserts] @TableName = 'Orders', _
@FromAndWhere = 'FROM Orders WHERE ShipCountry = ''Switzerland'''

/* Query will give result in this format

IF NOT EXISTS(SELECT TOP 1 1 FROM Orders WHERE [ShipCountry]='Germany')
    BEGIN 
    INSERT INTO [Orders] ([OrderID],[CustomerID],[EmployeeID],[OrderDate],[RequiredDate],_
                [ShippedDate],[ShipVia],[Freight],[ShipName],[ShipAddress],[ShipCity],[ShipRegion],_
                [ShipPostalCode],[ShipCountry])VALUES(10249,'TOMSP',6,'Jul  5 1996 12:00:00:000AM',_
                'Aug 16 1996 12:00:00:000AM','Jul 10 1996 12:00:00:000AM',1,11.6100,_
                'Toms Spezialitäten','Luisenstr. 48','Münster',NULL,'44087','Germany') 
    END
IF NOT EXISTS(SELECT TOP 1 1 FROM Orders WHERE [ShipCountry]='France')
    BEGIN 
    INSERT INTO [Orders] ([OrderID],[CustomerID],[EmployeeID],[OrderDate],[RequiredDate],_
                [ShippedDate],[ShipVia],[Freight],[ShipName],[ShipAddress],[ShipCity],[ShipRegion],_
                [ShipPostalCode],[ShipCountry])VALUES(10248,'VINET',5,'Jul  4 1996 12:00:00:000AM',_
                'Aug  1 1996 12:00:00:000AM','Jul 16 1996 12:00:00:000AM',3,32.3800,_
                'Vins et alcools Chevalier','59 rue de l''Abbaye','Reims',NULL,'51100','France') 
    END

    .
    .
    .

*/

Example of generating all inserts from “Orders” table on the basis of some condition (Where Clause) and with "If Exists" clause on the basis of multiple columns:

EXEC [dbo].[usp_CreateInserts] @TableName = 'Orders', _
@FromAndWhere = 'FROM Orders WHERE ShipCountry = ''Switzerland''', @CheckColList = 'ShipCountry,ShipVia'

/* Query will give result in this format

IF NOT EXISTS(SELECT TOP 1 1 FROM Orders WHERE [ShipVia]=2 AND [ShipCountry]='Switzerland')
    BEGIN 
    INSERT INTO [Orders] ([OrderID],[CustomerID],[EmployeeID],[OrderDate],[RequiredDate],_
                [ShippedDate],[ShipVia],[Freight],[ShipName],[ShipAddress],[ShipCity],[ShipRegion],_
                [ShipPostalCode],[ShipCountry])VALUES(10254,'CHOPS',5,'Jul 11 1996 12:00:00:000AM',_
                'Aug  8 1996 12:00:00:000AM','Jul 23 1996 12:00:00:000AM',2,22.9800,_
                'Chop-suey Chinese','Hauptstr. 31','Bern',NULL,'3012','Switzerland') 
    END
IF NOT EXISTS(SELECT TOP 1 1 FROM Orders WHERE [ShipVia]=3 AND [ShipCountry]='Switzerland')
    BEGIN 
    INSERT INTO [Orders] ([OrderID],[CustomerID],[EmployeeID],[OrderDate],[RequiredDate],_
                [ShippedDate],[ShipVia],[Freight],[ShipName],[ShipAddress],[ShipCity],[ShipRegion],_
                [ShipPostalCode],[ShipCountry])VALUES(10255,'RICSU',9,'Jul 12 1996 12:00:00:000AM',_
                'Aug  9 1996 12:00:00:000AM','Jul 15 1996 12:00:00:000AM',3,148.3300,_
                'Richter Supermarkt','Starenweg 5','Genève',NULL,'1204','Switzerland') 
    END

    .
    .
    .

*/

Example of generating all inserts from “Orders” table leaving identity columns:

EXEC [dbo].[usp_CreateInserts] @TableName = 'Orders', _
     @FromAndWhere = 'FROM Orders WHERE ShipCountry = ''Switzerland''', _
     @CheckColList = 'ShipCountry,ShipVia',@OmitIdentity =1

/* Query will give result in this format

IF NOT EXISTS(SELECT TOP 1 1 FROM Orders WHERE [ShipVia]=2 AND [ShipCountry]='Switzerland')
    BEGIN 
    INSERT INTO [Orders] ([CustomerID],[EmployeeID],[OrderDate],[RequiredDate],_
                [ShippedDate],[ShipVia],[Freight],[ShipName],[ShipAddress],[ShipCity],_
                [ShipRegion],[ShipPostalCode],[ShipCountry])VALUES('CHOPS',5,_
                'Jul 11 1996 12:00:00:000AM','Aug  8 1996 12:00:00:000AM',_
                'Jul 23 1996 12:00:00:000AM',2,22.9800,'Chop-suey Chinese','Hauptstr. 31',_
                'Bern',NULL,'3012','Switzerland') 
    END
IF NOT EXISTS(SELECT TOP 1 1 FROM Orders WHERE [ShipVia]=3 AND [ShipCountry]='Switzerland')
    BEGIN 
    INSERT INTO [Orders] ([CustomerID],[EmployeeID],[OrderDate],[RequiredDate],[ShippedDate],_
                [ShipVia],[Freight],[ShipName],[ShipAddress],[ShipCity],[ShipRegion],_
                [ShipPostalCode],[ShipCountry])VALUES('RICSU',9,'Jul 12 1996 12:00:00:000AM',_
                'Aug  9 1996 12:00:00:000AM','Jul 15 1996 12:00:00:000AM',3,148.3300,_
                'Richter Supermarkt','Starenweg 5','Genève',NULL,'1204','Switzerland') 
    END

    .
    .
    .

*/

Example of generating top "n" inserts from “Orders” table:

EXEC [dbo].[usp_CreateInserts] @TableName = 'Orders', _
@FromAndWhere = 'FROM Orders WHERE ShipCountry = ''Switzerland''', _
@CheckColList = 'ShipCountry,ShipVia',@Top = 10

Example of generating all inserts from “Orders” table leaving some columns. Here in this example, we have excluded "OrderId" and "EmployeeID" columns:

EXEC [dbo].[usp_CreateInserts] @TableName = 'Orders', _
@FromAndWhere = 'FROM Orders WHERE ShipCountry = ''Switzerland''', _
@CheckColList = 'ShipCountry,ShipVia',@ExcludeColList = '''OrderID'',''CustomerID'''

/* Query will give result in this format

IF NOT EXISTS(SELECT TOP 1 1 FROM Orders WHERE [ShipVia]=2 AND [ShipCountry]='Switzerland')
    BEGIN 
    INSERT INTO [Orders] ([EmployeeID],[OrderDate],[RequiredDate],[ShippedDate],_
                [ShipVia],[Freight],[ShipName],[ShipAddress],[ShipCity],[ShipRegion],_
                [ShipPostalCode],[ShipCountry])VALUES(5,'Jul 11 1996 12:00:00:000AM',_
                'Aug  8 1996 12:00:00:000AM','Jul 23 1996 12:00:00:000AM',2,22.9800,_
                'Chop-suey Chinese','Hauptstr. 31','Bern',NULL,'3012','Switzerland') 
    END
IF NOT EXISTS(SELECT TOP 1 1 FROM Orders WHERE [ShipVia]=3 AND [ShipCountry]='Switzerland')
    BEGIN 
    INSERT INTO [Orders] ([EmployeeID],[OrderDate],[RequiredDate],[ShippedDate],[ShipVia],_
                [Freight],[ShipName],[ShipAddress],[ShipCity],[ShipRegion],[ShipPostalCode],_
                [ShipCountry])VALUES(9,'Jul 12 1996 12:00:00:000AM','Aug  9 1996 12:00:00:000AM',_
                'Jul 15 1996 12:00:00:000AM',3,148.3300,'Richter Supermarkt','Starenweg 5',_
                'Genève',NULL,'1204','Switzerland') 
    END
    .
    .
    .

Example of generating all inserts from “Orders” table with owner name mentioned against objects:

EXEC [dbo].[usp_CreateInserts] @TableName = 'Orders', _
@FromAndWhere = 'FROM Orders WHERE ShipCountry = ''Switzerland''', _
@CheckColList = 'ShipCountry,ShipVia',@Owner = 'dbo'

/* Query will give result in this format

IF NOT EXISTS(SELECT TOP 1 1 FROM [dbo].[Orders] WHERE [ShipVia]=2 AND [ShipCountry]='Switzerland')
    BEGIN 
    INSERT INTO [[dbo].[Orders]] ([OrderID],[CustomerID],[EmployeeID],[OrderDate],[RequiredDate],_
                [ShippedDate],[ShipVia],[Freight],[ShipName],[ShipAddress],[ShipCity],_
                [ShipRegion],[ShipPostalCode],[ShipCountry])VALUES(10254,'CHOPS',5,_
                'Jul 11 1996 12:00:00:000AM','Aug  8 1996 12:00:00:000AM',_
                'Jul 23 1996 12:00:00:000AM',2,22.9800,'Chop-suey Chinese','Hauptstr. 31',_
                'Bern',NULL,'3012','Switzerland') 
    END
IF NOT EXISTS(SELECT TOP 1 1 FROM [dbo].[Orders] WHERE [ShipVia]=3 AND [ShipCountry]='Switzerland')
    BEGIN 
    INSERT INTO [[dbo].[Orders]] ([OrderID],[CustomerID],[EmployeeID],[OrderDate],[RequiredDate],_
                [ShippedDate],[ShipVia],[Freight],[ShipName],[ShipAddress],[ShipCity],_
                [ShipRegion],[ShipPostalCode],[ShipCountry])VALUES(10255,'RICSU',9,_
                'Jul 12 1996 12:00:00:000AM','Aug  9 1996 12:00:00:000AM',_
                'Jul 15 1996 12:00:00:000AM',3,148.3300,'Richter Supermarkt','Starenweg 5',_
                'Genève',NULL,'1204','Switzerland') 
    END
    .
    .
    .

*/

Points of Interest

• If “@CheckColList” is too long, it will fail, basically variables are not able to handle large data. I will try to resolve this issue in a future version.

History

• 11^th December, 2017: First version