Click here to Skip to main content
15,868,016 members
Articles / Database Development / SQL Server
Tip/Trick

Generate Insert script to extract data

Rate me:
Please Sign up or sign in to vote.
4.00/5 (2 votes)
18 Feb 2012CPOL1 min read 22.1K   5   5
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:
SQL
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)


Written By
Technical Lead Microlise
India India
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionHow about an IF EXISTS ... UPDATE... SET ... option? Pin
jvjessen4-Apr-12 19:48
jvjessen4-Apr-12 19:48 
QuestionUpdate Script Pin
Rishikesh_Singh19-Feb-12 16:49
Rishikesh_Singh19-Feb-12 16:49 
AnswerRe: Update Script Pin
chandan kumar8-Feb-19 15:55
chandan kumar8-Feb-19 15:55 
GeneralRe: Update Script Pin
Rishikesh_Singh21-Mar-19 2:26
Rishikesh_Singh21-Mar-19 2:26 
PraiseRe: Update Script Pin
chandan kumar21-Mar-19 2:41
chandan kumar21-Mar-19 2:41 

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

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