Click here to Skip to main content
Click here to Skip to main content

Generate Insert script to extract data

, 18 Feb 2012
Rate this:
Please Sign up or sign in to vote.
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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Rishikesh_Singh
Software Developer NCS,Singapore
Singapore Singapore
No Biography provided

Comments and Discussions

 
QuestionHow about an IF EXISTS ... UPDATE... SET ... option? Pinmemberjvjessen4-Apr-12 19:48 
QuestionUpdate Script PinmemberRishikesh_Singh19-Feb-12 16:49 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140902.1 | Last Updated 18 Feb 2012
Article Copyright 2012 by Rishikesh_Singh
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid