Generate Insert script to extract data

Introduction

This tool helps generate the Insert script on multiple tables based on Filter Criteria given by user.

Background

Many times, we are faced with the requirement in our project either during the development phase or support phase to get the Insert script generated for the data table.

SSMS Generate Scripts feature comes close to doing that but it has a limitation that we can’t provide the filter condition on the table and it also doesn’t generate the “IF EXIST” Check before inserting.

This led me to create this tool which can fill the gap left by SSMS. It has helped me immensely in my project and I hope it will help many of you guys looking for something like this.

This is a console application and you can reuse the code to have it as Windows application.

Using the Code

This is a Console application where the user needs to provide a connection string and Table names delimited by comma. User can provide the filter condition by putting pipe ('|') symbol and then filter conditon.

Let's assume that you want to create Insert Data script for 'Table1', 'Table 2' and Table 3 and want to have a filter condition on 'Table1' and 'Table 3' then you need to pass the following information to generate the data script

Table1|Code='ABC',Table2,Table3|Name like '%Rishi%'

Following are tables and Views used to get the information needed to generate the script. You can find the source code at the below link: https://docs.google.com/open?id=0B6ywA95u7w33OTJhNzYyZWEtYmMyNi00M2U4LWEyYzktZTUzNzY1YzNmNTE5[^]

Tables Used

sys.schemas 
sys.tables
sys.columns
sys.types 

Views Used

INFORMATION_SCHEMA.TABLE_CONSTRAINTS
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

The generated script will look like:

IF NOT EXISTS (Select 1 from Table1 WHERE Col1='Col1' AND Col2='Col2')
INSERT INTO Table1 (Col1,Col2,Col3,Col4) VALUES (1,'abc',null,'2012-01-01')

The code can easily be modified to generate Update script if the row is already there in the table.