insert command issue
-
Hi there,
what is the diffrent between insert into / select into?
regards,lit.
-
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).
-
This i know
im talking about optimization/perfomance issue
-
>>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.
-
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.
-
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).
-
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 -
Beautiful! Thank you.
I wasn't born stupid - I had to study.
-
Doing a SELECT INTO #TempTables can cause blocking in tempdb.
--------------------
Colt 45 - the original point and click interface
-
>>Doing a SELECT INTO #TempTables can cause blocking in tempdb.
In which version of SqlServer ? sys* tables in tempdb are row-locked in Sql2K.
-
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply