Inserting data with SELECT and UNION statements

Question

Inserting data with SELECT and UNION statements

ReamerXXVI

Ten Centuries

Points: 1379
More actions
January 22, 2013 at 5:19 pm

#393994

Hi,
Can someone please explain how inserting data with SELECT and UNION statements work, like in the following example;
insert student(student_name)
select 'abc'
union
select 'pqr'
union
select 'xyz'
I know what the result is but I do not understand how this works, or why you would want to do this.... any help,links,further reading appreciated.
Thanks.

Viewing 4 posts - 1 through 3 (of 3 total)

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

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 1

IMHO it's used mostly to save time when writing the code. It's like saying INSERT this set of data...and also this, oh wait, here's a few more rows to append to the operation, no, wait, I actually have some more...and handles it in more of a "batch style". As opposed to writing it in more of set-based operation - insert this into that, insert this into that...etc.

Both accomplish the same thing however there are performance impacts associated with both. Test.

For things like "examples" for SSC or for testing your own code, the UNION ALL approach is kind of the "norm"

Not sure this helps much but here's a few links where similar questions have been asked in the past:

http://www.ikriv.com/dev/db/SqlInsert/SqlInsert.html

http://www.sqlservercentral.com/Forums/Topic672861-338-1.aspx

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

ReamerXXVI Ten Centuries Points: 1379 More actions · Answer 2

Cool, thanks for the reply and the links.

I'll do some testing and check out the execution plans of the different methods. Seems like using the "Union" method could lessen to impact on the transaction log for large inserts due to it being executed as a single transaction.

MyDoggieJessie SSC-Forever Points: 44486 More actions · Answer 3

Definitely test, then test some more 😉 Increasing the volume of the data in the batch will have it's own overheard on your transaction log.

______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience