Bulk Copying and Deleting in OneTransaction

Question

In C# application I like to copy the table data from one server(SQLServer2000) to another server (SQLServer2005). I like to copy the data in single instance and delete the existing data in the SQL Server 2000 table. I need to do all this(Bulk copying and Deleting) in single transaction. How to achieve this?

Note: I am having two different sql server connections how to achieve this for single transaction

Answer 1

To minimise the duration of the transaction, I always do this by bulk-copying to a staging table (same schema, but different name - no indexes etc), and then once all the data is at the server, do something like:

BEGIN TRAN

DELETE FROM FOO

INSERT FOO ...
SELECT ...
FROM FOO_STAGING

COMMIT TRAN

DELETE FROM FOO_STAGING

(the transaction could be either in the TSQL or on the connection via managed code, or via TransactionScope; the TSQL could be either as command-text, or as a SPROC)

Answer 2

You can do this using linked servers, although I've never tried doing this between a SQL2005 and a SQL2000 instance. On your SQL2005 instance:

sp_addlinkedserver Sql2000Server --Only need to do this once on the server

BEGIN TRAN

INSERT INTO dbo.MyTable (id, column) 
    SELECT id, column FROM Sql2000Server.MyDatabase.dbo.MyTable
DELETE FROM Sql2000Server.MyDatabase.dbo.MyTable
--etc...

COMMIT TRAN

See Microsoft books online for the syntax to add / remove linked servers (http://msdn.microsoft.com/en-us/library/ms190479.aspx)

Answer 3

Further to the linked server suggestion, you can use SSIS as well, which would be my preferred method.