# Simpler MySQL 'insert into' command?

## Recommended Posts

As my database's column count expands at a seeminglessly cruel and unusual rate, I find myself making some pretty bad mistakes with aligning the data sets in my MySQL INSERT commands. For example: >INSERT INTO something (x,y,z,f,h,o,e,r,rr,rrr,p,pp,qr,delta,this,is,a,very,long,insert) VALUES ('5','3',f','yes','hi','which','letter','is','this?','oh drats', 'have I gone too far?','or not far enough','=/'); >kab00m! MySQL ERROR. Cannot match column to value! I'm wondering if the 'MySQL Connector Net' contains any wrappers around this, such as, say... MySQLInsertCommand.AddValue('columnname1','value1'); MySQLInsertCommand.AddValue('columnname2','value2'); MySQLReader = MySQLInsertCommand.Execute();

##### Share on other sites
I don't get it. Shouldn't you write that insert statement only once, anyway? Write it correctly that single time, and be done with it.

##### Share on other sites
Actually I have/had the same problem with a browser game I made once. While developing it I often created tables with lots of values, and was starting to get tired of making the dreaded insert query. And if your doing everything from MySQL CMD then you do have to do it over and over again.

To answer the question? There isn't a simpler way to do the insert query, sorry. Tough luck really, guess you'll just have to be more carefull :P

##### Share on other sites
Quote:
 Original post by ToohrVykI don't get it. Shouldn't you write that insert statement only once, anyway? Write it correctly that single time, and be done with it.

Sorta. Everytime I make modifications to the structure of my table (which I do quite often), I have to go back and hack away at this incredibly long insert statement. Although this may sound like a simple task, it gets incredibly difficult when the column list and the value list are separated by a couple pages of values (there are 634 columns in my table; a hundred of which have non indexed names).

I quickly hacked this function togethor, since I honestly can't handle dealing with misalignments any longer.

    /* A wrapper for generating long MySQL 'INSERT INTO' commands. */    public class cInsertMaker    {        List<string> TheColumns = new List<string>();        List<string> TheValues = new List<string>();        public void sInsertRecord(string TheColumn, string TheValue)        {            TheColumns.Add(TheColumn);            TheValues.Add(TheValue);        }        public string sGenerateInsert(string TheTable)        {            string TheReturn = "INSERT INTO " + TheTable + " (";            for (int TheLoop = 0; TheLoop < TheColumns.Count; TheLoop++)                TheReturn += TheColumns[TheLoop] + ",";            TheReturn = TheReturn.Substring(0, TheReturn.Length - 1);            TheReturn += ") VALUES (";            for (int TheLoop = 0; TheLoop < TheColumns.Count; TheLoop++)                TheReturn += "'" + TheValues[TheLoop] + "',";            TheReturn = TheReturn.Substring(0, TheReturn.Length - 1);            TheReturn += ");";            return TheReturn;        }    }

Yes, its soo terrible of code. But given the environment it will be used in, it's perfectly safe and of adequate performance. Shame a wrapper like this wasn't built into the connector though, but I suppose it would be slightly too specialised.

[Edited by - Thevenin on October 6, 2006 4:40:06 AM]

##### Share on other sites
INSERT INTO something SET x='5', y='3', z='f', ...
should avoid the problem of having to line up column names and values.

##### Share on other sites
Quote:
 Original post by Thevenin(there are 634 columns in my table; a hundred of which have non indexed names).

That many columns?!? Wow!
I think your database format is the actual problem here than. Split that huge table up in some more managable tables, is my advice.

##### Share on other sites
Quote:
Original post by DaBono
Quote:
 Original post by Thevenin(there are 634 columns in my table; a hundred of which have non indexed names).

That many columns?!? Wow!
I think your database format is the actual problem here than. Split that huge table up in some more managable tables, is my advice.

I agree - if you have that many columns in your tables, then you should refactor it.

John B

##### Share on other sites
Quote:
Original post by DaBono
Quote:
 Original post by Thevenin(there are 634 columns in my table; a hundred of which have non indexed names).

That many columns?!? Wow!
I think your database format is the actual problem here than. Split that huge table up in some more managable tables, is my advice.

Agreed. Having 634 columns is just fool (no offense intended) and very ineficient. Don't forget you are using a relational database management system. I really doubt that you got no redundancy in these columns.

##### Share on other sites
Not that you need another person saying it, but this really sounds like you need to think about your design. Are you aware of the different normalized forms of databases? If you normalized this may be easier to manage. If it already is normalized you might want to think about the design in general. That many columns is just unmanageable. If you really need to keep this massive table maybe using views (the latest MySQL supports them I believe) would be an option to break it up into manageable parts.

##### Share on other sites
My tables don't usualy have more than about 5 columns.

##### Share on other sites
My tables don't usualy have more than about 5 columns.

##### Share on other sites
I know it's already been said, but, if I had a 600-field table in my database, I would seriously consider using something other than a naked database. I would possibly serialize the object as XML or even binary data, keeping only the most used fields (an index, maybe variables you will want to select on). Another option would be to drop the database and think up something else.

##### Share on other sites
you may want to look into normalizing your database a little better, possibly make use of default values a little more as well.

##### Share on other sites
Quote:
 Original post by DaveMy tables don't usualy have more than about 5 columns.

## Create an account

Register a new account

• ### Forum Statistics

• Total Topics
628291
• Total Posts
2981862

• 11
• 10
• 10
• 11
• 17