copy data between tables

Question

I need to copy a large amount (~200,000) of records between two tables inside the same SQL Server 2000 database.

I can't change the original table to include the columns I would need, so the copy is the only solution.

I made a script with insert select statement. It works, but sometimes the .net form that triggers the stored procedure catches an exception with a timeout expired error.

Is there a more effective way to copy this many records around?

Any tips about how to check where the timeout occurred in the database?

Answer 1

INSERT (id,name) SELECT id,name FROM your_table WHERE your_condition

And i'd suggest you to put your form in a different thread so It won't freeze, you can also increase the timeout, it's in your connection string.

If you can't avoid the multiple insert, you can try to split them in smaller stack, for instance send only 50 query at a time.

Answer 2

Are you wanting to create an application to copy data between tables or is this just a one-off solution? If you only need to do this once, you should create a script to execute on the database server itself to copy the data you need to transfer between tables.

Answer 3

Are you using a SqlCommand to execute the stored procedure?

If so, set the CommandTimeout:

 myCmd.CommandTimeout = 360; //value is in seconds.