Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Inserting data with SELECT and UNION statements Expand / Collapse
Author
Message
Posted Tuesday, January 22, 2013 5:19 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, September 14, 2014 11:43 PM
Points: 79, Visits: 151
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.
Post #1410301
Posted Tuesday, January 22, 2013 8:53 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, September 18, 2014 10:38 PM
Points: 3,931, Visits: 7,160
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; They'll drag you down to their level and beat you with experience"
Post #1410323
Posted Tuesday, January 22, 2013 9:50 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, September 14, 2014 11:43 PM
Points: 79, Visits: 151
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.

Post #1410334
Posted Tuesday, January 22, 2013 9:55 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, September 18, 2014 10:38 PM
Points: 3,931, Visits: 7,160
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; They'll drag you down to their level and beat you with experience"
Post #1410335
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse