Click here to Skip to main content
12,243,038 members (43,845 online)
Click here to Skip to main content
Add your own
alternative version

Stats

83.5K views
1.1K downloads
36 bookmarked
Posted

INSERT\UPDATE Query Generator Based on Table Values

, 15 Feb 2006 GPL3
Rate this:
Please Sign up or sign in to vote.
This tool would help anyone to create insert\update query based on values in a table, without any data or with custom data
Sample Image - QueryGenerator.jpg

Introduction

This tool would help anyone to create insert\update query based on values in a table, without any data or with custom data.

Background

It has always been annoying for me to write insert\ update queries for a table having a lot of columns. It sometimes looks tiresome to copy and paste all the columns. Moreover, recently I came across an issue in an project where I added some values into a setting table and then needed to replicate the same at the client end. As I manually updated SQL table, there does not seem any easier way to replicate that at the client end without accessing their SQL Server box or using DTS.

Solution

To solve these issues, I developed this tool and hope it would help the person who has come across the same problem. Moreover, I think this is an ideal tool to increase your productivity when you are creating lots of insert\update queries either in stored procedure or application.

When you start this tool, you would be required to connect to a SQL Server. On being successfully connected, it will display a list of table and views in the dropdown table.The code to access list of table and view is:

string sql = "SELECT TABLE_NAME as Name, TABLE_NAME as Value _
	FROM Information_Schema.Tables";
sql += "  WHERE TABLE_TYPE IN ('BASE TABLE', 'VIEW') ORDER BY TABLE_TYPE, TABLE_NAME";
SqlDataAdapter da = new SqlDataAdapter(sql, _connection);
DataTable dt = new DataTable();
da.Fill(dt)

Once a user selects a table or view, the list of columns belonging to that table\view are displayed in a panel. These columns are displayed using checkbox with AutoScroll property of panel set to True for making it scrollable. The checkbox would help the user to select what column she/he wants in insert\update SQL.

The code to get a list of columns for a table or view is:

string[] restrictions = new string[4] { _database, null, objectName, null };
DataTable dt = _connection.GetSchema("Columns", restrictions);
return dt;
Where _database is your database name and ObjectName is name of tabel\view

You could filter the data from select query using filter text box. Once, you have done so, clicking on QUERY button would populate the grid with the columns selected in the panel.

Now, if you want an empty template of insert\update query, don't select any row and click on GENERATE buttons.

This would open another form with the generated query and also save the text into clipboard.
To create an insert\update query based on existing value in table, select the corresponding row and click GENERATE button.

You could also change any cell value(locally) and generate insert\update query with custom data. This tool caters to the proper datatype and appends single quotes to text columns. I have added the column type to the column header (for quicker development work) and extract them when creating queries.

Note: I have developed this in a couple of hours and have not tested it thoroughly, so if anyone finds a bug, please report it to me and I would be more than happy to solve that.

License

This article, along with any associated source code and files, is licensed under The GNU General Public License (GPLv3)

Share

About the Author

S Sansanwal
Architect
Australia Australia
No Biography provided

You may also be interested in...

Comments and Discussions

 
Generalwhen downloading source, file size is 0 Pin
Alexis Rzewski31-Aug-09 8:59
memberAlexis Rzewski31-Aug-09 8:59 
GeneralRe: when downloading source, file size is 0 Pin
S Sansanwal31-Aug-09 14:23
memberS Sansanwal31-Aug-09 14:23 
Generalsql storedprocedure Pin
murugavelk12-Nov-08 0:00
membermurugavelk12-Nov-08 0:00 
GeneralIn case identity insert is off Pin
yordan_georgiev21-Sep-08 20:10
memberyordan_georgiev21-Sep-08 20:10 
QuestionHow to get column info for sql table using sqldmo? Pin
lildiapaz8-Aug-07 10:34
memberlildiapaz8-Aug-07 10:34 
AnswerRe: How to get column info for sql table using sqldmo? Pin
S Sansanwal8-Aug-07 13:58
memberS Sansanwal8-Aug-07 13:58 
GeneralRe: How to get column info for sql table using sqldmo? Pin
lildiapaz9-Aug-07 12:13
memberlildiapaz9-Aug-07 12:13 
GeneralOracle compatibility Pin
Deepak Srivathsan23-Jul-07 4:25
memberDeepak Srivathsan23-Jul-07 4:25 
GeneralRe: Oracle compatibility Pin
S Sansanwal23-Jul-07 13:53
memberS Sansanwal23-Jul-07 13:53 
GeneralRe: Oracle compatibility Pin
Deepak Srivathsan24-Jul-07 13:06
memberDeepak Srivathsan24-Jul-07 13:06 
GeneralSuggestion Pin
neil young7-Feb-06 22:47
memberneil young7-Feb-06 22:47 
GeneralCool start Pin
Tom Pester2-Feb-06 12:32
memberTom Pester2-Feb-06 12:32 
GeneralRe: Cool start Pin
S Sansanwal2-Feb-06 20:46
memberS Sansanwal2-Feb-06 20:46 

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.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.160426.1 | Last Updated 15 Feb 2006
Article Copyright 2006 by S Sansanwal
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid