Tuesday, February 12, 2008

MS SQL Server is brain dead

Another post in the series "Why does this have to be so painful???"

I had to insert some data in a MS SQL Server database (don't ask, I just had to.) The same data was inserted into a MySQL database using a simple statement of the form:

INSERT INTO table1 (col1, col2, col3)
VALUES
(a,b,c),
(d,e,f),
(x,y,z);

I tried to do the same in MS SQL Server, and I was getting mysterious 'syntax error' messages, with no other explanation. In desperation, I only left one row of values in the statement, and boom, the syntax errors disappeared. I thought -- wait a minute, there's no way I can only insert one row at a time in MS SQL Server! But yes, that turned out to be the sad truth. I finally found a slightly better way to deal with multiple rows at a time in this blog post -- something extremely convoluted like this:

INSERT INTO table1 (col1, col2, col3)
SELECT
a, b, c
UNION ALL
SELECT
d, e, f
UNION ALL
SELECT
x, y, z

(be very careful not to end your statement with a UNION ALL)

What can I say, my post title says it all. Another thing I noticed while desperately googling around: there is precious little information about SQL Server on forums etc., in comparison with a huge wealth of info on MySQL. And the best information on SQL Server is on pay-if-you-want-to-see-the-answers forums such as experts-exchange.com. Not surprising, but sad.

7 comments:

Anonymous said...

SQL Server 2008 has this, it is called row constructors
pre 2008 you have to use UNION (like you did)

here is a 2008 example
http://sqlblog.com/blogs/louis_davidson/archive/2007/12/05/2008-initializing-table-data-with-row-constructors.aspx

and

http://sqlblog.com/blogs/denis_gobo/archive/2007/12/06/3741.aspx


>>And the best information on SQL Server is on pay-if-you-want-to-see-the-answers forums such as experts-exchange.com. Not surprising, but sad.

If you posted your question here:

http://groups.google.com/group/microsoft.public.sqlserver.programming/topics

I am sure you would have gotten your answer within 10 minutes

Denis

Steve Lindemann said...

>>And the best information on SQL Server is on pay-if-you-want-to-see-the-answers forums such as experts-exchange.com. Not surprising, but sad.

(psst. Google cache. don't tell anyone.)

Grig Gheorghiu said...

Denis -- thanks for the comments and the clarification for 2008. You may be right regarding the slqserver google group, but the general impression when you do a google search on SQL Server-related stuff is still that there is a dearth of information.

Grig

Grig Gheorghiu said...

zweiterlinde -- you're right, I used google cache too in conjunction with paid forums, but hey, I'm not telling anyone either :-)

grig

Evgeny Zislis said...

Experts Exchange?
Just scroll down ... after all those commercials and links to nowhere there are the plain-text answers. Or if not plain-text, then rot13.

Grig Gheorghiu said...

evgeny -- yeah, you're right, sometimes there are some clear-text answers at the very bottom of the page. Weird.

Anonymous said...

Yeah and UNION ALL SELECT d, e, f is invalid SQL :-(

Plus triggers get called multiple times for the multi-statement version.

Steve Lee

Modifying EC2 security groups via AWS Lambda functions

One task that comes up again and again is adding, removing or updating source CIDR blocks in various security groups in an EC2 infrastructur...