how long to copy records into another table? 1

[557]

if there are about 1100000 records in a table of one database and i try to copy them into another table of another database, how long would it ideally take?

 

[JamesLean]

This all depends on numerous factors, eg size of each row, indexes/triggers on the tables, server usage etc.

But in general you should be looking at a number of seconds rather than minutes/hours.

--James

 

[557]

thanks Jameslean. my tables concerned had 10 fields and no indexes. when i do the insert using a single sql statement like,

insert into table2 select * from table1

it gives, transaction log full - error.

when i did the insert by small groups of records. it just took 4 minutes. will increasing the size of log file help? or is there a way to increase the speed at which the log file grows? what do you think will help me do it in one single sql statement?

 

[ESquared]

To insert 5000 records at a time, in batches, easily:

SET ROWCOUNT 5000

WHILE EXISTS (
   SELECT 1
   FROM Table1 T1 LEFT JOIN Table2 ON T1.Key = T2.Key
   WHERE T2.Key IS NULL
)
   INSERT INTO Table2
      SELECT T1.*
      FROM Table1 T1 LEFT JOIN Table2 ON T1.Key = T2.Key
      WHERE T2.Key IS NULL

SET ROWCOUNT 0

You might also look up doing this inside a bulk-logged transaction which could help with your transaction log.

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[yksvaan]

First you should consider, is the database you are inserting into a reporting/datawarehouse kind of non-transactional database? Can you imagine you can make a full backup of the database every time backuping is needed , so you won't need "daily" transaction logging backups ?

If you answer Yes, in case of sql2000, goto Database's properties, Options tab, and change Recovery model to Simple, not Full (in case of sql7 it's checkbox Truncate log on checkpoint).[When Recov.m is Simple, you make full db backups, and when recov.m is Full, you make "simple" backups]. In sql2k, in Transaction log tab, you can define auto-grow, won't go into details, possibly define maximun file size to something (2 Gigs?, of course you need the disk space).
After this you probably want to empty the transaction log. In Query Anayzer in master db context, execute command DUMP TRANSACTION yourdatabasename WITH NO_LOG. You also might want to shrink the logfile, but I won't go into that now.

Cheers

 

[ESquared]

Good points, yksvaan, there may be no need for a transaction log at all.

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[yksvaan]

There still might be the need to batch the insert process, but I don't have the experience to discuss that. One thing though, if you can reserve the target table for the load only, please do so by locking the whole table exclusively. This should speed up the insert. You do it like this:
INSERT INTO dest_db.dbo.dest_table WITH (TABLOCKX) (column list)
SELECT .... -- [ could even lock source if possible FROM src_table WITH (TABLOCK) ]

And if you want to fully empty the destination table before load, use instead of DELETE FROM:
TRUNCATE TABLE dest_db.dbo.dest_table
(if you have identity column in the table, you need to know one thing more)

Cheers