SQL Server - Copy Data Between Tables

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 inser开发者_JS百科t 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?

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.

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.

Are you using a SqlCommand to execute the stored procedure?

If so, set the CommandTimeout:

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

1>Compare two database with redgate data compare since other table is empty the script which will generate after comparing will be all inserts.Select insert for that table only. 2>Use multiscript from redgate just add those script to multiscript and execute on that database table it will keep on executing till complete and then you can compare if u have all data correctly. 3> If you don't want to use multiscript create a command line application to just insert the data .