Transferring data between tables is a common task of database maintenance. It is used when updating database schemas or when you need to migrate data to a log or history table. There are 2 common methods for performing the transfer the first is done by use of a WHERE NOT EXISTS sub query, while the less common known method is by setting up and utilizing a Trigger, which removes rows from the old table as soon as they get inserted into the new table.
WHERE NOT EXISTS Method
The most common method of transferring data between tables in a database that i have seen is the combination of ROWCOUNT and a WHERE NOT EXISTS clause. How this method works is that you set your ROWCOUNT to the batch size then you insert into your table to be transferred to from a select of your old table where the current record does not exist in your transfer to table. A sample code snippet showing this method is:
DECLARE @ErrorId Integer, @RowCtrInserted Integer SET ROWCOUNT 10000 SELECT @RowCtrInserted = 1, @ErrorId = @@error WHILE (@ErrorId = 0 AND @RowCtrInserted > 0) BEGIN INSERT INTO LOG_HIST ( ID, USER_EMP_ID, APP_NM, ACTION_NM, ACTION_TS, IP_AD ) SELECT ID, USER_EMP_ID, APP_NM, ACTION_NM, ACTION_TS, IP_AD FROM LOG log WHERE NOT EXISTS ( SELECT * FROM LOG_HIST hist WHERE log.ID = hist.ID ) SELECT @RowCtrInserted = @@rowcount, @ErrorId = @@error END IF @ErrorId=0 BEGIN TRUNCATE TABLE LOG END
As can be seen in the code this method loops while a record exists in the log table that is not present in the log_hist table. This requires an additional query every time the batch size is met, and can be a performance problem on large database tables.
Temporary Trigger Method
The second method that can be used to transfer data between two tables is the use of a temporary insert trigger on the table being transferred to. How it works is that the trigger is defined to delete the newly inserted record from the old table. What this means is that for every iteration of the insert loop as determined by the batch size a sub query to determine if a record already exists in the new table is not necessary as records are deleted as soon as they are transferred. Below is a sample sql script setting up the trigger and performing the transfer, in addition it contains error checking and print statements.
create trigger LOG_HIST_TR on LOG_HIST for insert as delete LOG from LOG, inserted where 1=1 AND LOG.ID = inserted.ID go print "Transferring data from LOG to LOG_HIST" go declare @err int, @n int select @n=0 print 'Will use batch size of 10000 rows' while exists (select * from LOG) begin INSERT LOG_HIST ( ID, USER_EMP_ID, APP_NM, ACTION_NM, ACTION_TS, IP_AD ) SELECT TOP 10000 ID, USER_EMP_ID, APP_NM, ACTION_NM, ACTION_TS, IP_AD FROM LOG select @err=@@error, @n=@n+@@rowcount if @err=0 begin dump tran USERACTIVITY with truncate_only print '%1! rows transferred', @n end else begin raiserror 20001 'Failed to transfer data from table LOG to table LOG_HIST. Please recover data from these two tables' break end end go print "Checking data transfer completion" go if not exists (select * from LOG) begin drop trigger LOG_HIST_TR end else begin raiserror 20001 'Failed to transfer data from LOG into LOG_HIST' end
In the above example after each batch iteration we output the total number of records transferred, as well as cleaning up the database log so that we don’t max out the allocated space. Once an error is thrown or no more records exists we drop our temporary trigger. You will notice that no truncating of the table we transferred data from is needed as the trigger was responsible for removing them. The additional benefit to using this method is that if a transfer is interrupted no time or data is loss and the transfer can pick up right were it left off without performing an additional WHERE NOT EXISTS sub query.