sql v2K, we have two tables --- current and history -- there is an identity value in the current table (ID), which, of course, is generated by sql upon insert. The history table is supposed to contain an exact copy of the data that was in current table.... recently i noticed it, too, had ID, but it was also an IDENTITY value. so, recordA copied from current to history is no longer identical, as a new ID is being generated when the data is copied from current to historical.
so, i extracted all the data and wrote it to a new table, same structure, same ID column, but it is no longer IDENTITY, it is just INT
now, here's the problem. we have an insert procedure which writes to BOTH tables.
1. IF it is current day, it only writes to current.
2. IF it is within the past 7 days, it writes to BOTH tables.
3. IF it is older than the past 7 days, it only writes to the historical table.
At present, it is failing on 2 and 3, because historical table needs that ID, which is being autogenerated into current table.
I can just require the front-end to pass an additional attribute for @ID, but then I open myself to the same problem. What if they're hitting current AND historical? The ID is autogenerated in current, and passed into historical --- they are no longer the same.
I am wondering --- IF the datecheck (see below) finds that it has to be written to current AND historical, is there any means of getting the ID that just got generated in current, and passing that into historical?
1. i check IF DATEDIFF(day,@time,getdate
())<=0 -- if so, it is written only to current
2. i check IF DATEDIFF(day,@time,getdate
()) BETWEEN 1 AND 7 -- if so, it is written to current and then to historical
3. i check IF DATEDIFF(day,@time,getdate
())>7 -- if so, it is only written to historical
my problem is with that 2nd check -- the writes into BOTH tables. i don't want to create different ID's for the same record being written to the historical table
any ideas?
