INSERT INTO vs. SELECT for inputting values

Question

INSERT INTO vs. SELECT for inputting values

mgodinez

SSC Enthusiast

Points: 124
More actions
October 5, 2009 at 5:35 pm

#134280

Dear Greater Transact-SQL Intellect,
My apologies for asking such a redimentary question...
I was reading Jeff Moden's article: Cross Tabs and Pivots, Part 1 - http://www.sqlservercentral.com/articles/T-SQL/63681/
Excellent article by the way.
And I'm just wondering about the way he is inputting data into his #SomeTable1 table.
The example shows the following:
CREATE TABLE # SomeTable1
(
Year SMALLINT
Quarter TINYINT
Amount DECIMAL (2,1)
)
SELECT 2006, 1, 1.1 UNION ALL
SELECT 2006 2, 1.2 UNION ALL
etc.
What the difference between using the SELECT statement to input data as opposed to:
INSERT INTO #SomeTable1
VALUES (2006, '1', '1.1')
INSERT INTO #SomeTable1
VALUES (2006, '2', 1.2')
etc.
?
Thanks.
Regards,
Mike G.
Seattle, WA

Viewing 8 posts - 1 through 7 (of 7 total)

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

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 1

The example using a set of SELECT statements linked by UNION ALLs is a single INSERT of the set of all rows. The other example executes a single INSERT for each individual row. There is much less overhead for the single INSERT of all rows, so it runs faster.

P.S. The word is "rudimentary." 😉

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 2

I'd be curious to know if anyone has tested this. It's one transaction v many, and I would tend to agree with Bob, but on anything less than hundreds or thousands of rows, I wouldn't expect "faster" to be noticeable.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

Steve Jones - Editor (10/5/2009)
I'd be curious to know if anyone has tested this. It's one transaction v many, and I would tend to agree with Bob, but on anything less than hundreds or thousands of rows, I wouldn't expect "faster" to be noticeable.

I seem to recall some while back that Jeff or someone had posted a test showing the UNION was faster.

Either way, I like it because it really cuts down on visual clutter in the query plan.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 4

I use it a lot in demonstration code for two reasons...

1. It cuts down on the actual visual clutter and takes less horizontal and vertical space.

2. I'm lazy. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 5

mgodinez (10/5/2009)
Excellent article by the way.

Thanks for the kudo, Mike. I appreciate it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 6

You're absolutely right, Steve. At that number of rows, hardly anything makes a noticeable difference.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

mgodinez SSC Enthusiast Points: 124 More actions · Answer 7

Hello,

Much obliged for the replies. I think I understand now.

Agreeed, w/ such a small amount of inserted data...I didn't notice too much of a time difference, running either code. But thousands of lines, I would think the impact would be felt.

Thanks again.

Regards,

Mike G.

Seattle, WA