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

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

Yes.


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

Michael
--
Michael Peppler Data Migrations, Inc.
mpeppler (AT) peppler (DOT) 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

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" <getabhijit (AT) indiatimes (DOT) com> wrote

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.

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 ...

getabhijit (AT) indiatimes (DOT) com (Abhi) wrote in message news:<67db1745.0409151239.76469e78 (AT) posting (DOT) google.com>...