Hi,

Can I copy a table with 69 million records from one database to another in
the same server using DTS package? Or I have to create a stored procedure to
read and insert, for example 50000 records each time (in this case we will
have a slow process)?

Does DTS use transaction log like regular insert statements?

The table has about 30 columns. The datatype of most of them is CHAR or
VARCHAR.

Thank you,
Rosie

Hello rosie,

Yes you can certainly copy a table with 69 million rows using DTS

You can also use a proc to do this. (you could even put the proc inside DTS)

You can read the whole table in one go but remember that it will be kept
in the logspace until the whole lot commits. Batches will allow you to commit
the rows earlier and let the log breathe

It can use TXNs yes.

1. Enable transactions at the package level (Package Properties)
2. On Success between each task
3. In the workflow properties for each task (check "Join transaction if
present","Rollback Transaction on Failure")
4. Make sure MSDTC is started


NOTE: Not everything supports joining a transaction




Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

Hi,

if you use the bulk insert to copy the rows from 1 table to another, the log
generated depends on the database log setup.
if the log setup of target database is FULL, then EVERYTHING is logged.
if its set to Bulk Log or simple, then bulk insert commands are logged.

the log is linked to the command executed to the database NOT the program
used to load the data, you can use another tool to load your data, until its
a bulkinsert command, there is no log

so, if you do the copy using a storeprocedure, you'll generate a log. Using
DTS or not.

"Rosie" <Rosie (AT) discussions (DOT) microsoft.com> wrote

Thank you.

"Jéjé" wrote:

http://www.codeproject.com/useritems/DTS__VBNET_.asp