Archive for March 2013
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.
How to export table data into a SQL insert script?
Have you ever had a table that you wanted to drop and recreate but you need all the data inside it. Perhaps it took you a lot of time to generate this data.
Well, you can save the data out as a SQL insert script if you want. Here is how:
- Open Microsoft SQL Server Management Studio and login.
- Right-click on the database and choose Tasks | Generate Scripts.
Note: The first time you do this you will see and Introduction page. Click Next. You can click the check box next to Do not show this page again and you won’t see this screen. - On the Choose Objects screen, click Select specific database objects.
- Expand Tables and click the check box next to the table you want to save and click Next.
- On the Set Scripting Options screen, click Advanced.
- Scroll down and under Types of data to script select Data only and click OK.
- Under File name select a file name to save to and click Next.
- Click Next, Next, Finish.
You now have a script to insert your data.