SQL INSERT statement syntax is poorly designed

Am I on the only in the technical community that is completely annoyed by SQL’s INSERT statement syntax? Especially with long INSERT queries.

Why in the world do we have to have the column and its value separated.

Imagine this table, which is an overly simplified example only version of a Person table.

FirstName MiddleName LastName Birthday UserName Password PhoneNumber Address SignupDate LastUpdated LastPasswordChange

Please not that this table only has 10 columns. I am working with tables with three of four times as many columns. Also these columns are pretty simple. Many columns have name like PK_D or other cryptic titles.

So wouldn’t it be nice to have a syntax that more easily connects the column and the value?

Lets look at the insert syntax:

INSERT INTO Person (FirstName, MiddleName, LastName, Birthday, UserName, Password, PhoneNumber, Address, SignupDate, LastUpdated, LastPasswordChange)
VALUES ('John', 'J.', 'Doe', '3/27/1977', 'jdoe', 'p@ssw0rd!, '555-555-5555, '1234 ABCD Street SomeCity, Utah 81234', '12/1/2012', '3/20/2013', '3/13/2013')

Ok, so you can start to see how even with these easy and well-known columns, it starts to get confusing as to which order you need to enter data. Also, you have to scroll right to left a lot. Also, in whichever order you type your column names, you have to enter you data.

Even with every value on its own line, this is still not very clear.

INSERT INTO Person (
FirstName, 
MiddleName, 
LastName, 
Birthday, 
UserName, 
Password, 
PhoneNumber, 
Address, 
SignupDate, 
LastUpdated, 
LastPasswordChange)
VALUES (
'John', 
'J.', 
'Doe', 
'3/27/1977', 
'jdoe', 
'p@ssw0rd!, 
'555-555-5555, 
'1234 ABCD Street SomeCity, Utah 81234', 
'12/1/2012', 
'3/20/2013', 
'3/13/2013')

Now it is a lot of lines, and you have just swapped extensive left and right scrolling to up and down scrolling. Remember, this is a small table. Some tables have many times more columns.

Of course you can get around some of this using default values and allowing NULLs so you don’t have to insert values for those columns. But if you have 30+ required columns, which the database I am working with has, then that doesn’t help. No, I am not in control of the database or I would likely change it.

Why is the syntax not something that is order insignificant?

INSERT INTO Person
FirstName='John', MiddleName='J.', LastName='Doe', Birthday='3/27/1977'
UserName='jdoe', Password='p@ssw0rd!, PhoneNumber='555-555-5555,
Address='1234 ABCD Street SomeCity, Utah 81234', SignupDate='12/1/2012',
LastUpdate='3/20/2013', LastPasswordChange='3/13/2013'

Or all on its own line:

INSERT INTO Person
FirstName='John', 
MiddleName='J.', 
LastName='Doe', 
Birthday='3/27/1977'
UserName='jdoe', 
Password='p@ssw0rd!, 
PhoneNumber='555-555-5555,
Address='1234 ABCD Street SomeCity, Utah 81234', 
SignupDate='12/1/2012',
LastUpdate='3/20/2013', 
LastPasswordChange='3/13/2013'

Is it just me who thinks that the standard property=value syntax is a quite a bit more readable and a lot more writable?

I am sure there are all kinds of reasons why this syntax isn’t used. I am not saying property=value syntax is a fix to the horrible INSERT syntax. I am just saying that the INSERT syntax is horrible and I would recommend that future version of SQL provide and more readable a more writable alternative.

4 Comments

  1. Hello there! This article could not be written much better!
    Reading through this post reminds me of my previous roommate!
    He constantly kept preaching about this. I am going
    to send this post to him. Pretty sure he'll have a great read. Thank you for sharing!

  2. DinoB says:

    I am not sure why, but I totally agree it gets quite complicated.

    • Rhyous says:

      I know, right? I've been using SQL for years. Before I just accepted the poorly designed syntax. Now, I guess I just see it for what it is: poor design and failure to improve syntax over time.

      • MeghansUncle2 says:

        So just do what I do when something like this bugs you, write a parser. Write the thing in your syntax and run it through the parser to convert it to sql that the machine likes. It's not like it's that hard to do. Be part of the solution, not part of the problem.

Leave a Reply to DinoB

How to post code in comments?