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

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

Comments and Discussions

 
Generalwhen downloading source, file size is 0 PinmemberAlexis Rzewski31-Aug-09 8:59 
GeneralRe: when downloading source, file size is 0 PinmemberS Sansanwal31-Aug-09 14:23 
Generalsql storedprocedure Pinmembermurugavelk12-Nov-08 0:00 
GeneralIn case identity insert is off Pinmemberyordan_georgiev21-Sep-08 20:10 
QuestionHow to get column info for sql table using sqldmo? Pinmemberlildiapaz8-Aug-07 10:34 
AnswerRe: How to get column info for sql table using sqldmo? PinmemberS Sansanwal8-Aug-07 13:58 
GeneralRe: How to get column info for sql table using sqldmo? Pinmemberlildiapaz9-Aug-07 12:13 
GeneralOracle compatibility PinmemberDeepak Srivathsan23-Jul-07 4:25 
GeneralRe: Oracle compatibility PinmemberS Sansanwal23-Jul-07 13:53 
GeneralRe: Oracle compatibility PinmemberDeepak Srivathsan24-Jul-07 13:06 
GeneralSuggestion Pinmemberneil young7-Feb-06 22:47 
GeneralCool start PinmemberTom Pester2-Feb-06 12:32 
GeneralRe: Cool start PinmemberS Sansanwal2-Feb-06 20:46 

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 | Terms of Use | Mobile
Web04 | 2.8.141223.1 | Last Updated 15 Feb 2006
Article Copyright 2006 by S Sansanwal
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid