Hi , 

I appriciate if someone calrifies.

As per Sybase manuals(T-Sql), "Select into" is a two step process first 
step create the table and insert the data into table. 

Whether my assumption is correct or not?

When creating the table Server will have lock on system tables, During the 
insertion of data it will not hold the lock on System tables.

Here I would like to know what are other adavantages I can get by using 
"select into"

In my devlopment I would like use more "select into"s than Create & Insert. 
Which reduces my logging pretty much. 

Thanks in Advance,
Kenny

Yes, to what you said.

SELECT into is a 2-piece process and the INSERT part of it does a 
non-logged insert process.  This is compared to the logged insert done by a 
stand-alone INSERT statement.

The create and drop of temp tables whether done via SELECT INTO or CREATE 
TABLE #tmp will both do exclusive locks on system tables that are held 
until the log page is written out.

More SELECT INTOs to create #tmp tables is better.  However, trying to not 
use #tmp tables at all is also a better choice.  #tmp tables do not come 
free - because of the exclusive table locks on system tables, you can get 
blocking conditions in tempdb if you do alot of #tmp table activity.  One 
customer of mine was doing at least 100+ #tmp table creates/drops per 
second.  Not always a good thing.

John McVicker
Inventa Technologies, Inc.

I almost exclusively use select into's and hardly ever use
insert/update/delete for the intermediate results of my processing.  With
that said, my main application code is written for nightly
maintenance/interace related code.  So, I do not have to worry about high
volume select/into's causing contention in tempdb.  I come into my customer
businesses and rewrite their nightly jobs which are taking hours and hours
using inserts/updates/deletes during the intermediate steps and I change the
intermediate steps to use select/into's and the boss sees dramatic
improvement in the nightly jobs.   The customer now has plenty of time to
get all of the processing done now.

I'm all for select/into's if its not going to be high volume executions of
the code.

Bob Densmore




<Kenny> wrote in message
news:36A39DE4A045CA4000083FDC85256D14.00083FEB85256D14@webforums...
> Hi ,
>
> I appriciate if someone calrifies.
>
> As per Sybase manuals(T-Sql), "Select into" is a two step process first
> step create the table and insert the data into table.
>
> Whether my assumption is correct or not?
>
> When creating the table Server will have lock on system tables, During the
> insertion of data it will not hold the lock on System tables.
>
> Here I would like to know what are other adavantages I can get by using
> "select into"
>
> In my devlopment I would like use more "select into"s than Create &
Insert.
> Which reduces my logging pretty much.
>
> Thanks in Advance,
> Kenny

What about transaction support ?
Business actually based on transactions.

Regards,
Eugene

Bob Densmore wrote:

> I almost exclusively use select into's and hardly ever use
> insert/update/delete for the intermediate results of my processing.  With
> that said, my main application code is written for nightly
> maintenance/interace related code.  So, I do not have to worry about high
> volume select/into's causing contention in tempdb.  I come into my customer
> businesses and rewrite their nightly jobs which are taking hours and hours
> using inserts/updates/deletes during the intermediate steps and I change the
> intermediate steps to use select/into's and the boss sees dramatic
> improvement in the nightly jobs.   The customer now has plenty of time to
> get all of the processing done now.
>
> I'm all for select/into's if its not going to be high volume executions of
> the code.
>
> Bob Densmore
>
> <Kenny> wrote in message
> news:36A39DE4A045CA4000083FDC85256D14.00083FEB85256D14@webforums...
> > Hi ,
> >
> > I appriciate if someone calrifies.
> >
> > As per Sybase manuals(T-Sql), "Select into" is a two step process first
> > step create the table and insert the data into table.
> >
> > Whether my assumption is correct or not?
> >
> > When creating the table Server will have lock on system tables, During the
> > insertion of data it will not hold the lock on System tables.
> >
> > Here I would like to know what are other adavantages I can get by using
> > "select into"
> >
> > In my devlopment I would like use more "select into"s than Create &
> Insert.
> > Which reduces my logging pretty much.
> >
> > Thanks in Advance,
> > Kenny

We have transaction support when it comes time to apply the manipulations
back to the database.  I'm primarily referring to "intermediate results"
where #tmp tables are used.

Bob

"Eugene Korolkov" <ekorolkov@davidsohn.com> wrote in message
news:3EAD3577.72188BFE@davidsohn.com...
> What about transaction support ?
> Business actually based on transactions.
>
> Regards,
> Eugene
>
> Bob Densmore wrote:
>
> > I almost exclusively use select into's and hardly ever use
> > insert/update/delete for the intermediate results of my processing.
With
> > that said, my main application code is written for nightly
> > maintenance/interace related code.  So, I do not have to worry about
high
> > volume select/into's causing contention in tempdb.  I come into my
customer
> > businesses and rewrite their nightly jobs which are taking hours and
hours
> > using inserts/updates/deletes during the intermediate steps and I change
the
> > intermediate steps to use select/into's and the boss sees dramatic
> > improvement in the nightly jobs.   The customer now has plenty of time
to
> > get all of the processing done now.
> >
> > I'm all for select/into's if its not going to be high volume executions
of
> > the code.
> >
> > Bob Densmore
> >
> > <Kenny> wrote in message
> > news:36A39DE4A045CA4000083FDC85256D14.00083FEB85256D14@webforums...
> > > Hi ,
> > >
> > > I appriciate if someone calrifies.
> > >
> > > As per Sybase manuals(T-Sql), "Select into" is a two step process
first
> > > step create the table and insert the data into table.
> > >
> > > Whether my assumption is correct or not?
> > >
> > > When creating the table Server will have lock on system tables, During
the
> > > insertion of data it will not hold the lock on System tables.
> > >
> > > Here I would like to know what are other adavantages I can get by
using
> > > "select into"
> > >
> > > In my devlopment I would like use more "select into"s than Create &
> > Insert.
> > > Which reduces my logging pretty much.
> > >
> > > Thanks in Advance,
> > > Kenny
>