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.
Subscribe to:
Post Comments (Atom)
-
A short but sweet PM Boulevard interview with Jerry Weinberg on Agile management/methods. Of course, he says we need to drop the A and actu...
-
Here's a good interview question for a tester: how do you define performance/load/stress testing? Many times people use these terms inte...
-
Update 02/26/07 -------- The link to the old httperf page wasn't working anymore. I updated it and pointed it to the new page at HP. Her...
7 comments:
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
>>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.)
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
zweiterlinde -- you're right, I used google cache too in conjunction with paid forums, but hey, I'm not telling anyone either :-)
grig
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.
evgeny -- yeah, you're right, sometimes there are some clear-text answers at the very bottom of the page. Weird.
Yeah and UNION ALL SELECT d, e, f is invalid SQL :-(
Plus triggers get called multiple times for the multi-statement version.
Steve Lee
Post a Comment