Fastest Way to copy a fields table to table in SP

Question

Fastest Way to copy a fields table to table in SP

ngkh

Mr or Mrs. 500

Points: 531
More actions
October 30, 2003 at 1:15 pm

#101735

I was wondering, if i got fields in table A wanted to do some transformation and copy it to table B, what is the fastest way in a store procedure? i have to do
insert into B
select field1
, field2
etc.
from A
do i suppose to enable "select into / bulk copy" for faster performance? or in a database properties i have already put simple as logging, instead of full and select into/bulk copy..
which one perform more logging ? select into / bulk copy or simple???????
Edgar

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 1

quote:
I was wondering, if i got fields in table A wanted to do some transformation and copy it to table B, what is the fastest way in a store procedure? i have to do

Because you want some transformation to data, you may try DTS.

quote:
insert into B
select field1
, field2
etc.
from A

insert into statement is fully logged, Doesn't matter whether you set database recovery mode to 'simple' or 'select into / bulk copy'. Setting database to 'simple' recovery model will truncate the log by system at checkpoint automatically.

quote:
do i suppose to enable "select into / bulk copy" for faster performance? or in a database properties i have already put simple as logging, instead of full and select into/bulk copy..
which one perform more logging ? select into / bulk copy or simple???????

The Bulk-Logged Recovery model (same as select into / bulk copy in SQL Server 7.0)provides best performance and minimal log space usage for certain large-scale or bulk copy operations. These operations are minimally logged:

SELECT INTO.

Bulk load operations (bcp and BULK INSERT).

CREATE INDEX (including indexed views).

text and image operations (WRITETEXT and UPDATETEXT).

Jonathan SSC-Insane Points: 20427 More actions · Answer 2

If Table B is to start out empty, then you could truncate it, drop it and then use a SELECT statement rather than INSERT:



SELECT Field1, Field2, ...

INTO TableB

FROM TableA

This will use the bulk copy process, so if your recovery model is Simple or Bulk-Logged it will be faster than using INSERT.

If Table B does not first get emptied, you might get better performance by using bcp to write out the transformed data and then using BULK INSERT to get it into Table B.

Bulk-Logged and Simple models both do the same amount of logging, but the Simple model has the additional overhead of truncating the log on every checkpoint.

--Jonathan