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:
- From any table you want
- On the basis of conditions
- With “
If Exists” clause based on columns you provide - Including or excluding identity column values
- 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:
- I should be able to generate scripts on the base of any conditions like we can select any number of rows based on conditions.
- I should be able to generate scripts with “
If Exists” Clause as I am not sure if data already exists in prod. - I may like to exclude excluding identity column values.
- 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:
- Download attachment from this article.
- 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'
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'''
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'
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
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'''
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'
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
- 11th December, 2017: First version
