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.

8 comments:

Denis The SQL Menace 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

zweiterlinde 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 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

cottsak said...

Try Stackoverflow next time instead of EE.