insert command issue

Question

Post reply

insert command issue

Lital

SSC Eights!

Points: 901
More actions
February 9, 2005 at 10:03 am

#66452

Hi there,
what is the diffrent between insert into / select into?
regards,lit.

Viewing 11 posts - 1 through 10 (of 10 total)

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

PW-201837 SSC-Insane Points: 20805 More actions · Answer 1

Insert into requires a table to exist already (and gives an error if the table does not exist).

Select Into creates the table (and gives an error if the table already exists).

Lital SSC Eights! Points: 901 More actions · Answer 2

This i know

im talking about optimization/perfomance issue

PW-201837 SSC-Insane Points: 20805 More actions · Answer 3

>>This i know

It would assist others, then, if you asked a specific question, so as not to waste other's time answering questions you don't need answered.

A "Select Into" creates the table, therefore the table cannot have any indexes, constraints, triggers or index fillfactors defined at the time of insert. It is therefore likely to execute quicker than an "Insert Into" on an existing table, since an existing table may require additional disk IO for index maintenance, additonal processing & IO for any triggers that fire and addtional joins to reference other tables to ensure referential integrity of the inserted data.

Farrell Keough SSCoach Points: 17414 More actions · Answer 4

What about #TempTables? Especially ones which do not contain any indexes, contraints, etc. One of our folks insists that SELECT INTO is far more inefficient than building the table ahead of time.

I wasn't born stupid - I had to study.

olavho Say Hey Kid Points: 706 More actions · Answer 5

In addition to those mentioned above:

"Select into" is one of the bulk-loading methods available in SQLServer, and will therefore follow a slightly different & simpler logging model than "insert into". This is however dependent on the configuration of the database (Recovery model).

With respect to performance, "Select into" is the far best option of the two.

Note that the some of column meta-data are copied from the source of the data, so I would suggest that you verify that the schema you get is how you want it to be. (I've had some troubles, for example with the collation settings).

noeld SSC Guru Points: 96590 More actions · Answer 6

It is NOT true. "Select Into" is a non logged operation therefore extremely fast. When you have a table already created you have no choice but to use insert into wich is a logged operation. Select into can also be used to replicate the table structure of a ... well table without knowing the full structure before hand.

But Select into is not a panacea, it does have its problems specially locking issues in server objects and that's the reason MS recommends the create temp table first and then use insert into but not because of performance!

HTH

* Noel

Farrell Keough SSCoach Points: 17414 More actions · Answer 7

Beautiful! Thank you.

I wasn't born stupid - I had to study.

philcart SSC-Forever Points: 47796 More actions · Answer 8

Doing a SELECT INTO #TempTables can cause blocking in tempdb.

--------------------
Colt 45 - the original point and click interface

PW-201837 SSC-Insane Points: 20805 More actions · Answer 9

>>Doing a SELECT INTO #TempTables can cause blocking in tempdb.

In which version of SqlServer ? sys* tables in tempdb are row-locked in Sql2K.

noeld SSC Guru Points: 96590 More actions · Answer 10

sys* tables in tempdb are row-locked in Sql2K

what about NORMAL sys* tables

select * into #Tbl1 from MYTABLE <-- Effectively lock the schema on the DB not on tempdb for the duration of the query

the use of select * into #Tbl1 from MYTABLE where 1=2 is neat to minimize the time while the schema is locked

* Noel