Will this cause transaction log full?

[Abhi]

Hi All
I have a table (e.g employee) containing millions of records. If I
do the following

select *
into new_table_name /* this can be a physical table or a temp table
*/
from employee

No where clause, I want to insert all the data from employee table to
the new table.
Will sybase treat the above statement as a single
transaction and won't commit unless it is completed? And is there any
risk of transaction log becoming full.

Thanks and Best Regards
-Abhi

[Michael Peppler]

On Wed, 15 Sep 2004 13:39:22 -0700, Abhi wrote:

> Hi All
> I have a table (e.g employee) containing millions of records. If I
> do the following
>
> select *
> into new_table_name /* this can be a physical table or a temp table
> */
> from employee
>
> No where clause, I want to insert all the data from employee table to
> the new table.
> Will sybase treat the above statement as a single
> transaction and won't commit unless it is completed?

Yes.

> And is there any
> risk of transaction log becoming full.

Depends on the size of the log, and the size of the "employee" table.

Michael
--
Michael Peppler Data Migrations, Inc.
mpep...@peppler.org http://www.peppler.org/
Sybase T-SQL/OpenClient/OpenServer/C/Perl developer available for short or
long term contract positions - http://www.peppler.org/resume.html

[Geoffrey Scholtes]

My understanding of this is that as this is a 'select into' operation it is
only minimally logged. Only the allocation of data pages is tracked (so that
rollback is possible), not the actual changes for each data row.

You will therefore use up substantially fewer log pages than an
'insert..select' statement.

Be aware that you cannot dump the transaction log after enabling select
into/bulk copy/pllsort and making minimally logged changes to the database
with select into. Use dump database instead.

"Abhi" <getab...@indiatimes.com> wrote in message
news:67db1745.04091...@posting.google.com...

[Byrocat]

> On Wed, 15 Sep 2004 13:39:22 -0700, Abhi wrote:
>
> > Hi All
> > I have a table (e.g employee) containing millions of records. If I
> > do the following
> >
> > select *
> > into new_table_name /* this can be a physical table or a temp table
> > */
> > from employee
> >

As Michael said, you'll probably fill the log if the table is large
enough and the log small enough.

Best solution to this is to BCP the contents of EMPLOYEE out to a flat
file, do the query with a where clause such that you've only copied
the table (eg, where employee_id = 0), and then BCP the flat file into
the new table.

Only other solution would be to slice things into smaller chunks using
a script with dumping the transaction log between each step. Use the
unique index on employee to determine which records are processed in
each pass.

Determine the number of passes you need to make to ensure that the
transaction log stays fairly empty (< 45% full), especially if this is
a critical production database.

If you do it via the script, DO NOT do things inside a WHILE statement
-- it gets classified as a single transaction for the structure.

[Sara Law]

Hi Abhi,

Try doing a subset and note the effect on your log ...

set rowcount 100000

insert table1
select * from employee ...

Do a "dbcc checktable (syslogs)" before and after (this may take a
while if your log is huge) and then see if you can take the whole hit.
There are other ways to accomlish what you wish but it depends on
database settings, size of table, etc.

Also, I would not recommend the "select into" clause in tempdb ...
this will lock the tempdb system tables for the duration of the query
and will basically stop tempdb activity. Obviously this is to be
avoided in production.

Good Luck!

Sara ...

getab...@indiatimes.com (Abhi) wrote in message news:<67db1745.04091...@posting.google.com>...

[Ed Avis]

bdea...@sympatico.ca (Byrocat) writes:

>>>select *
>>>into new_table_name /* this can be a physical table or a temp table */
>>>from employee

>As Michael said, you'll probably fill the log if the table is large
>enough and the log small enough.

I thought that 'select into' didn't use the log; this is why you need
to enable the select into/bulkcopy option before using it, because it
performs a non-logged transaction.

--
Ed Avis <e...@membled.com>

[Michael Peppler]

The inserts aren't logged, the table allocations *are* logged. So the
chance of filling the log is fairly small unless the source table is very
large...

Michael
--
Michael Peppler - mpep...@peppler.org - http://www.peppler.org/
Sybase DBA/Developer
Sybase on Linux FAQ: http://www.peppler.org/FAQ/linux.html