Inserting data with SELECT and UNION statements

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Inserting data with SELECT and UNION...

Inserting data with SELECT and UNION statements

Rate Topic

Display Mode

Topic Options

Author

Message

ReamerXXVI

Posted Tuesday, January 22, 2013 5:19 PM

SSC Rookie

Group: General Forum Members
Last Login: Sunday, April 28, 2013 10:53 PM
Points: 47, Visits: 48

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

MyDoggieJessie

Posted Tuesday, January 22, 2013 8:53 PM

SSCrazy

Group: General Forum Members
Last Login: Friday, May 17, 2013 8:50 AM
Points: 2,035, Visits: 3,759

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" Wink

Post #1410323

ReamerXXVI

Posted Tuesday, January 22, 2013 9:50 PM

SSC Rookie

Group: General Forum Members
Last Login: Sunday, April 28, 2013 10:53 PM
Points: 47, Visits: 48

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

MyDoggieJessie

Posted Tuesday, January 22, 2013 9:55 PM

SSCrazy

Group: General Forum Members
Last Login: Friday, May 17, 2013 8:50 AM
Points: 2,035, Visits: 3,759

Definitely test, then test some more Wink

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" Wink

Post #1410335

« Prev Topic | Next Topic »

Permissions