Multiple Inserts

Question

Post reply

Multiple Inserts

Steve Jones - SSC Editor

SSC Guru

Points: 736172
More actions
January 16, 2012 at 9:58 pm

#74243

Comments posted to this topic are about the item Multiple Inserts

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply

M&M SSC-Insane Points: 21700 More actions · Answer 1

M&M

SSC-Insane

Points: 21700

January 16, 2012 at 9:59 pm

#1434396

Good one Steve, Thank you.

M&M

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Straight up question.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

cengland0 SSCertifiable Points: 6102 More actions · Answer 3

I love that insert method and MySQL has had it available for so long. Unfortunately, it took Microsoft ages to add that feature. Too bad it wasn't available in the earlier versions of SQL Server 2000 and 2005. Because of that, I will not use that insert method because (as I've stated in other threads) I like to make my SQL portable. If I built it and it works on 2008 only, I couldn't put the same code on a 2005 server.

Some of you might say that I shouldn't worry about the portability and just program for what I have available (for performance reasons) but I've had to change servers so many times I already have experience with wasting time reprogramming incompatible code. I don't want to subject myself to that in the future. My minimum requirement is SQL Server 2005. If it doesn't work in 2000, I don't care because I don't know of any servers in my business still on that platform.

Britt Cluff SSCertifiable Points: 5083 More actions · Answer 4

Good straight forward question. Thanks Steve.

http://brittcluff.blogspot.com/

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 5

A good question, and it's hard to see how anbody could contest the correct answer.

Although in the spirit of nitpicking, I could argue that the format one should use would include the column list in the INSERT statement. And the semicolon statement terminator, that in a future version will no longer be optional.

INSERT dbo.mytable (Column1, Column2) VALUES ('Test', 'Test2'), ('Test3', 'Test4'), ('test5', 'test6');

EDIT: Removed the color tag after a few failed attempts to use it - I hope just using bold text emphasises the changes sufficiently.

EDIT: Added back the color tag after Paul told me what I did wrong.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

Hugo Kornelis (1/17/2012)
A good question, and it's hard to see how anbody could contest the correct answer.
Although in the spirit of nitpicking, I could argue that the format one should use would include the column list in the INSERT statement. And the semicolon statement terminator, that in a future version will no longer be optional.
INSERT dbo.mytable (Column1, Column2) VALUES ('Test', 'Test2'), ('Test3', 'Test4'), ('test5', 'test6');
EDIT: Removed the color tag after a few failed attempts to use it - I hope just using bold text emphasises the changes sufficiently.

Actually if you wanted to nitpick you missed it. Including column names is best practice which doesn't mean you should use it. The correct answer is also the ONLY answer with valid syntax. Maybe your nitpicking can be the wording. Which one of these has the correct syntax? 😀

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Steve Jones - SSC Editor SSC Guru Points: 736172 More actions · Answer 7

Hugo Kornelis (1/17/2012)
A good question, and it's hard to see how anbody could contest the correct answer.
Although in the spirit of nitpicking, I could argue that the format one should use would include the column list in the INSERT statement. And the semicolon statement terminator, that in a future version will no longer be optional.

Picky, picky.

Yes, I should have the semi-colon in there. I do like to show code the way I'd want it written in production, and the semi-colon is one I'm struggling to remember as I write since it's an old habit to forgo it.

The columns, it's a good idea, and I've gotten slightly lazy over the years with this.

Andre Guerreiro SSCertifiable Points: 7319 More actions · Answer 8

Easy and straight-forward question.

Thank you.

And when I need to use something similar in MSSQL 2000 or 2005 I write this:

INSERT INTO dbo.Table(Column1, Column2)

SELECT 'Value1', 'Value2' UNION ALL

SELECT 'Value3', 'Value4' UNION ALL

SELECT 'Value5', 'Value6';

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA

Toreador SSChampion Points: 11365 More actions · Answer 9

Hugo Kornelis (1/17/2012)
A good question, and it's hard to see how anbody could contest the correct answer.

How long before someone complains that the correct answer is an error message saying there's no such table as my table 😉

EL Jerry SSCertifiable Points: 7063 More actions · Answer 10

I agree with cengland0 about portability. You never know where your code will end up running, even if it is a secured ("closed"?) environment. As a former Tech Support Analyst, we got several headaches when new versions of SQL Server were released because of code deprecation and incompatibility, so I wave the "As Standard As Possible" flag.

Cheers,

"El" Jerry.

"A watt of Ottawa" - Gerardo Galvan

To better understand your help request, please follow these best practices.[/url]

OzYbOi d(-_-)b SSCarpal Tunnel Points: 4054 More actions · Answer 11

OzYbOi d(-_-)b

SSCarpal Tunnel

Points: 4054

January 17, 2012 at 10:41 am

#1434744

tks Steve.

Thomas Abraham SSChampion Points: 10761 More actions · Answer 12

Toreador (1/17/2012)
Hugo Kornelis (1/17/2012)
A good question, and it's hard to see how anbody could contest the correct answer.
How long before someone complains that the correct answer is an error message saying there's no such table as my table 😉

Not me, but about those column names ... certainly it's not good design practice to use such generic column names. :hehe:

[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn

Paul White SSC Guru Points: 150467 More actions · Answer 13

Hugo Kornelis (1/17/2012)
EDIT: Removed the color tag after a few failed attempts to use it - I hope just using bold text emphasises the changes sufficiently.

Like this...? (the stupid trick here is to put double-quotes around the hex colour value *and* the leading # sign. Awesome.)

HugoHugoHugoHugoHugoHugo

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 14

SQL Kiwi (1/17/2012)
Like this...? (the stupid trick here is to put double-quotes around the hex colour value *and* the leading # sign. Awesome.)

Thanks, Paul. I missed the double-quotes.

I have now edited my post to show the missing bits in red.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/