Difference between SELECT INTO AND INSERT INTO

Hi,
I would appreciate if any one can tell me what exactly is the difference
between
1) select * into # abc from table1
2) Insert into #abd select * from table1
assuming tables are indexed properly what is the performance benefit, how
locks are placed in this, how locks will be escalated and duration.
is second statement better than first then why ?Sanjay
It is easy to test it by yourself ,does not?
> 1) select * into # abc from table1
You don't need to create a table before this command , the SELECT INTO
statement creates a new table and populates it with the result set of the
SELECT.
It is possible that you'll see some perfomance improvment by using this
method as well as possible locks that may occur.

> 2) Insert into #abd select * from table1
You will have to issue CREATE TABLE #Table (col INT,......) before the
statement
I'd prefer the second one , but you did not mention about a table variable
you can use as well
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:967886AB-7697-4C36-AC0B-0D9B1D0EBB39@.microsoft.com...
> Hi,
> I would appreciate if any one can tell me what exactly is the difference
> between
> 1) select * into # abc from table1
> 2) Insert into #abd select * from table1
> assuming tables are indexed properly what is the performance benefit, how
> locks are placed in this, how locks will be escalated and duration.
> is second statement better than first then why ?|||Hi Sanjay
The main deifference between the 2 queries is creation of the table:
SELECT * INTO .. tries to creates a new table each time its executed.
The table is created in the first run, and from next time if the table
exist, the query is not executed
INSERT INTO <TABLE> needs to have a <table> to proceed sucessfully.
INSERT INTO does'nt create a new table
Please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Sanjay" wrote:

> Hi,
> I would appreciate if any one can tell me what exactly is the difference
> between
> 1) select * into # abc from table1
> 2) Insert into #abd select * from table1
> assuming tables are indexed properly what is the performance benefit, how
> locks are placed in this, how locks will be escalated and duration.
> is second statement better than first then why ?|||Thanks for reply, but can you tell me how locking architecture in first
scenario.
1) what type of locks will be placed on table1 when used with SELECT INTO,
will it be shared lock on table1 till the records get inserted into temporar
y
table.
and what locking will takes place when i do insert into #tab select * from
table1.
assuming i have some 10000 records to insert.
thanks for all the help
--
Sanjay
"Uri Dimant" wrote:

> Sanjay
> It is easy to test it by yourself ,does not?
> You don't need to create a table before this command , the SELECT INTO
> statement creates a new table and populates it with the result set of the
> SELECT.
> It is possible that you'll see some perfomance improvment by using this
> method as well as possible locks that may occur.
>
> You will have to issue CREATE TABLE #Table (col INT,......) before the
> statement
>
> I'd prefer the second one , but you did not mention about a table variable
> you can use as well
>
>
> "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> news:967886AB-7697-4C36-AC0B-0D9B1D0EBB39@.microsoft.com...
>
>|||Sanjay
By deafult SQL Server implements ROW LOCK but it depends on load of data
and many other things
If persist to use SELECT INTO command run SELECT * INTO #Test FROM Table
WHERE 1=2 to get a structure of the table and then perfom INSERT INTO
command
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:680473F9-4933-409E-B3E5-FADEDEE9EAE4@.microsoft.com...[vbcol=seagreen]
> Thanks for reply, but can you tell me how locking architecture in first
> scenario.
> 1) what type of locks will be placed on table1 when used with SELECT INTO,
> will it be shared lock on table1 till the records get inserted into
> temporary
> table.
> and what locking will takes place when i do insert into #tab select * from
> table1.
> assuming i have some 10000 records to insert.
> thanks for all the help
> --
> Sanjay
>
> "Uri Dimant" wrote:
>|||examnotes (chandra@.discussions.microsoft.com) writes:
> The main deifference between the 2 queries is creation of the table:
> SELECT * INTO .. tries to creates a new table each time its executed.
> The table is created in the first run, and from next time if the table
> exist, the query is not executed
In fact you get an error if the table does already exist.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||examnotes (Sanjay@.discussions.microsoft.com) writes:
> Thanks for reply, but can you tell me how locking architecture in first
> scenario.
> 1) what type of locks will be placed on table1 when used with SELECT
> INTO, will it be shared lock on table1 till the records get inserted
> into temporary table.
> and what locking will takes place when i do insert into #tab select * from
> table1.
> assuming i have some 10000 records to insert.
The locks on table1 should be the same in both cases.
If you run will full recovery, there is little difference between SELECT
INTO and INSERT, but see below for a war story.
If you run with bulk-logged recovery, SELECT INTO is minimally logged.
Instead of logging each row, SQL Server logs only the page allocation. I'm
uncertain of what happens when you have simple recovery. Now, since
simple recovery is what you have in tempdb, this is what applies. My guess
goes for minimally logged. Thus, with SELECT INTO you write fewer log
records, and you can therefor get better performance.
On the other hand, it takes more resources to create the table. I once
tried to track down a performance problem, and was running Profiler and
all that. I had basically given up on the main problem, but decided that I
should look at a trigger where there was some non-low numbers (they were
not exceedingly high.) What I had in that trigger was
SELECT * INTO #inserted FROM inserted
The point with this is that the virtual table "inserted" is slow to
work with. Now, this function I was looking into performed a loop, so
there were many updates on that table within that loop - and the loop
was one big transaction. So that table #inserted was created each time.
This created lots of locks in tempdb, both on the system tables and
locks on the extents that no longer were in use. I realised that the
SELECT INTO was a recent change into that trigger, and the performance
problem was new. I reverted to the old version without the temp table -
and the main performance problem that I had had was gone.
When I later researched this a bit more, I found that using CREATE TABLE
instead of SELECT INTO took less amount of locking resources, although
there still were a few.
The solution in this particular case is to use a table variable.
If I am to give a recommendation, is to use CREATE TABLE, unless you
have a very good reason to use SELECT INTO.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland
Very useful info, thanks
I read recently that in SQL Server 2005 virtual tables (deleted and
insreted) are 'real' work table and not 'virtual' managed in memory tables.
I that truth?
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96AC80ED12727Yazorman@.127.0.0.1...
> examnotes (Sanjay@.discussions.microsoft.com) writes:
> The locks on table1 should be the same in both cases.
> If you run will full recovery, there is little difference between SELECT
> INTO and INSERT, but see below for a war story.
> If you run with bulk-logged recovery, SELECT INTO is minimally logged.
> Instead of logging each row, SQL Server logs only the page allocation. I'm
> uncertain of what happens when you have simple recovery. Now, since
> simple recovery is what you have in tempdb, this is what applies. My guess
> goes for minimally logged. Thus, with SELECT INTO you write fewer log
> records, and you can therefor get better performance.
> On the other hand, it takes more resources to create the table. I once
> tried to track down a performance problem, and was running Profiler and
> all that. I had basically given up on the main problem, but decided that I
> should look at a trigger where there was some non-low numbers (they were
> not exceedingly high.) What I had in that trigger was
> SELECT * INTO #inserted FROM inserted
> The point with this is that the virtual table "inserted" is slow to
> work with. Now, this function I was looking into performed a loop, so
> there were many updates on that table within that loop - and the loop
> was one big transaction. So that table #inserted was created each time.
> This created lots of locks in tempdb, both on the system tables and
> locks on the extents that no longer were in use. I realised that the
> SELECT INTO was a recent change into that trigger, and the performance
> problem was new. I reverted to the old version without the temp table -
> and the main performance problem that I had had was gone.
> When I later researched this a bit more, I found that using CREATE TABLE
> instead of SELECT INTO took less amount of locking resources, although
> there still were a few.
> The solution in this particular case is to use a table variable.
> If I am to give a recommendation, is to use CREATE TABLE, unless you
> have a very good reason to use SELECT INTO.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>|||Thanks , this has answers my most of the questions.
--
Sanjay
"Erland Sommarskog" wrote:

> examnotes (Sanjay@.discussions.microsoft.com) writes:
> The locks on table1 should be the same in both cases.
> If you run will full recovery, there is little difference between SELECT
> INTO and INSERT, but see below for a war story.
> If you run with bulk-logged recovery, SELECT INTO is minimally logged.
> Instead of logging each row, SQL Server logs only the page allocation. I'm
> uncertain of what happens when you have simple recovery. Now, since
> simple recovery is what you have in tempdb, this is what applies. My guess
> goes for minimally logged. Thus, with SELECT INTO you write fewer log
> records, and you can therefor get better performance.
> On the other hand, it takes more resources to create the table. I once
> tried to track down a performance problem, and was running Profiler and
> all that. I had basically given up on the main problem, but decided that I
> should look at a trigger where there was some non-low numbers (they were
> not exceedingly high.) What I had in that trigger was
> SELECT * INTO #inserted FROM inserted
> The point with this is that the virtual table "inserted" is slow to
> work with. Now, this function I was looking into performed a loop, so
> there were many updates on that table within that loop - and the loop
> was one big transaction. So that table #inserted was created each time.
> This created lots of locks in tempdb, both on the system tables and
> locks on the extents that no longer were in use. I realised that the
> SELECT INTO was a recent change into that trigger, and the performance
> problem was new. I reverted to the old version without the temp table -
> and the main performance problem that I had had was gone.
> When I later researched this a bit more, I found that using CREATE TABLE
> instead of SELECT INTO took less amount of locking resources, although
> there still were a few.
> The solution in this particular case is to use a table variable.
> If I am to give a recommendation, is to use CREATE TABLE, unless you
> have a very good reason to use SELECT INTO.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>|||Uri Dimant (urid@.iscar.co.il) writes:
> Very useful info, thanks
> I read recently that in SQL Server 2005 virtual tables (deleted and
> insreted) are 'real' work table and not 'virtual' managed in memory
> tables. I that truth?
I don't know of any changes to inserted/deleted, but that does not mean
that there are not any. There are tons of new features in SQL 2005, and
I have certainly missed more than one. So I can neither confirm nor deny.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp