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)
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...
-
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