SQL: Export data to new table and update old data simultaneously
I want to export data from one table into a new one with a nightly job.
To prevent generate dublicates, I implemented a column named "ExportState" in the source table which is 0 for not exported and 1 for exported.
My problem is, that I want to export the data and then setting the State to 1. But I can not make a INSERT INTO ... SELECT and then UPDATE Statements because it is possible that additional Data would inserted to the source table while the export routine runs. So I would at the end UPDATE the ExportState to 1 on records which I never INSERTed to the destination table.
Do you have suggestions to the following solutions ?
A. INSERT INTO ... SELECT and UPDATE ExportState row by row B. Take a Snaphot INSERT and UPDATE ExportState of the snapshoted Data
Which makes more sense ?
The second problem: The source and destination tables are on different SQL Servers and database instances. Ideas ?
I would create a stored procedure to perform the task.
Within the stored procedure create a table variable or temp table. Insert the data from the source table where ExportState = 0 into the temp table. (If you have a primary key on this table just store the primary key in your temp table.)
Perform your insert statement from source table to destination table.
Using your temp table, perform your update statement to set ExportState = 1 for each record in your temp table.
Wrap all of this within a transaction.
BEGIN TRAN DECLARE @Exported TABLE (PK INTEGER NOT NULL); INSERT INTO @Exported (PK) SELECT PK FROM SourceTable WHERE ExportState = 0; INSERT INTO @DestinationTable (Field Names) SELECT FieldNames FROM SourceTable s INNER JOIN @Exported e ON s.PK = e.PK WHERE s.ExportStatus = 0; UPDATE s SET ExportStatus=1 FROM SourceTable s INNER JOIN @Exported e on s.PK =e.PK; COMMIT TRAN
Invoke the stored procedure from your nightly job.
To connect to databases on other SQL Servers, look into using Linked Servers. You should be able to configure one under the "Server Objects" folder in SSMS 2008. Here is a link to more info if you are interested...http://msdn.microsoft.com/en-us/library/ff772782.aspx