Friday, February 24, 2012

Avoid interrupting of BEGIN-END?

Hello,
I am searching for a way to get data moved from table to table. Meanwhile, data is being added to the table.
The batch query I thought of was the following:

BEGIN
SELECT (Column1, Column2) INTO Table2 (Column1, Column2)
FROM Table1;
TRUNCATE Table1;
(process data from Table2)
TRUNCATE Table2;
END

Problem is, it musn't be interrupted.
But data is being inserted continuously by another process, so the data from that process need to be copied to a cache table of sql server, or something like that.
Another thought of mine was letting that process insert it's data via a synonym (in this example called "Synonym1"). The application making use of Table 2 can either make use of Table1, that doesn't matter.
The batch query would be like the following:

BEGIN
ALTER Synonym1=Table1_Temp;
(process data from Table1);
TRUNCATE Table1;
ALTER Synonym1=Table1;
SELECT (Column1, Column2) INTO Table1 (Column1, Column2)
FROM Table1_Temp;
TRUNCATE Table1_Temp;
END

But the ALTER function does not exist for the SYNONYM function in SQL Server.
It needs to be done with drop and create.
Problem is, in that split second the other program wich is adding data to the database, will make an error because it cannot find that synonym.
Does anybody know how to make such processes uninterruptible?
Thanks in advance Smile

Can you modify the structure of the tables in question or are they set?

If you can change them then I would add a processing field to table1 which defaults to Null (or possibly 0 if bit). The first step is to set that flag for all the records in table1. Then all your other operations (copying and deleting) use that in a where clause to only affect those records. Any records added after you start will have that value at the default and therefore not be affected (until the process runs the next time.

The only problem I can see is if this process runs twice with overlap - and the old version would have that problem as well. In that case what you could do is use a unique ID for each run and place that rather than simply a flag value in to the processing field. The most obvious is a GUID but as those are 16-bytes there is probably a better solution (particularly if this is as active a table as you imply).

A problem with transferring between tables as you show in your sample is if you try to use automatic identity columns as these will probably not be unique between the tables.

No comments:

Post a Comment