INSERTING 20Mill Records into a Table with 100Mill Records..

Hi, Iam new to SQL Srvr 2005 with a Oracle Background..

I have three tables
Table 1 (100 Mill Rows)
Table 2 (20 Mill Rows)
Table 3 (10 Mill Rows)

INSERT INTO Table1
select Table2.* from Table 2
where exists (select 1 from Table3
where Table2.xyz = Table3.xyz
and Table2.abc = Table3.abc);

Whats the most efficient way to do this..
Iam already DISABL'ng the Indexes before the Insert on Table 1
Also -- Whats the SQLSRVR's equivalent to Rollback Segment?

I would suggest to use a join and be sure to have indexes in table2 and table3 by the columns used in the join.

- index on table2 by (xyz, abc)

- index on table3 by (xyz, abc)

The index could be also by (abc, xyz), but it will depend on the order of an existing constraint like primary key or foreign key, or in case there not a constraint, then the selectivity of those columns..

INSERT INTO Table1

select

Table2.*

from

Table 2 inner join Table3

on Table2.xyz = Table3.xyz and Table2.abc = Table3.abc

AMB

|||

Inserting 20 mil rows at one time will create a log of Transaction Log activity.

IF, and that is a big IF, this is a singular operation, and if there is no other activity in the database, you may wish to change the recovery model to 'SIMPLE' (after first making a backup.)

Then do the import in batches of 100k rows - this will greatly reduce the logging pressure and could make a radical difference in speed.

When finished, return the recovery model to the previous setting. AND then make a full backup.

|||

If you go with changing the Recovery Model to simple and back, you'll want to be sure to run a full back-up immediately after reverting back.

Switching to the Simple model breaks the log chain and a full back-up is required to establish a new chain.

|||

Thanks, Dale,

I should have explicitly mentioned that (assumptions, assumptions, etc.)