Best way to select and insert a huge table in the same database

insert A (...) select ... from B inner join C on ....
will create huge transact log. Any better way?See 'Minimally Logged Operations' and 'Bulk-Logged Recovery Model' on BOL to
see if that can help in your specific case.
Ben Nevarez, MCDBA, OCP
Database Administrator
"nick" wrote:

> insert A (...) select ... from B inner join C on ....
> will create huge transact log. Any better way?|||On Sun, 26 Mar 2006 14:49:44 -0800, nick wrote:

>insert A (...) select ... from B inner join C on ....
>will create huge transact log. Any better way?
Hi Nick,
If you can't use bulk insert in your situation, try doing it in batches:
INSERT A (...)
SELECT ...
FROM B
INNER JOIN C
ON ...
WHERE SomeColumn <= 10000
CHECKPOINT
-- BACKUP LOG <database_name> TO <backup_device>
INSERT A (...)
SELECT ...
FROM B
INNER JOIN C
ON ...
WHERE SomeColumn > 10000
AND SomeColumn <= 20000
CHECKPOINT
-- BACKUP LOG <database_name> TO <backup_device>
(...)
INSERT A (...)
SELECT ...
FROM B
INNER JOIN C
ON ...
WHERE SomeColumn > 90000
CHECKPOINT
-- BACKUP LOG <database_name> TO <backup_device>
If your recovery model is simple, you don't need the backup log
commands. For full or bulk-logged recovery, uncomment these statements.
Make sure that this batch is NOT included in a transaction. If you need
to be able to rollback the entire operation in case of any error, make a
full database backup just before starting and restore to it if things go
wrong.
Hugo Kornelis, SQL Server MVP