May 26, 2005 at 1:51 pm
I've got a task and I'm not sure of the best way to get it done...
Data is coming in to Table A continuously (or almost continuously) from a time clock. Every 5 minutes or so, I need to copy everything in Table A to Table B and then delete the contents of Table A.
My problem is that I don't know how to deal with the data that comes in to Table A between the "Insert into Table B from Table A" and the "Delete * from Table A"... I certainly don't want to delete anything in Table A that didn't make it into Table B. The only key is using a field with an identity property.
Is this one of those "rare" situations that cursors would be legimitately be used?
Maybe use a Stored Procedure instead?
Any guidance would be greatly appreciated!
Bob
May 26, 2005 at 2:03 pm
select @maxi = Max(id) from tableA
Begin tran
insert into TableB (Fld1,Fld2) select Fld1, Fld2 from TableA where Id < @maxi
if @@error <> 0
begin
rollback Tran
return
end
delete from TableA where Id < @maxi
if @@error <> 0
rollback Tran
else
commit tran
* Noel
May 26, 2005 at 3:18 pm
You would also want to read in Books Online about transaction isolation level.
For example an article "Customizing Transaction Isolation Level" it has examples.
Yelena
Regards,Yelena Varsha
May 26, 2005 at 3:36 pm
Thanks Noel,
I tried what you suggest and it works to a point (I'm pretty new at this so please consider this source!)
Here is the Stored Procedure I set up...****************************
CREATE PROCEDURE [dbo].[acsp_AC_PUNCHIMPORT_TO_AC_PUNCHIMPORT_TEMP]
@maxPunchID int
as
set nocount on
select @maxPunchID = Max(PNCHIMPID) from AC_PUNCHIMPORT
Begin tran
insert into AC_PUNCHIMPORT_TEMP (REQ_CODE, EMPID, PDATE, PTIME, WORKRULE, LABORACCT, TIMEZONE, COMMENTTEXT)
select REQ_CODE, EMPID, PDATE, PTIME, WORKRULE, LABORACCT, TIMEZONE, COMMENTTEXT from AC_PUNCHIMPORT where PNCHIMPID <= @maxPunchID
if @@error <> 0
begin
rollback Tran
return
end
delete from AC_PUNCHIMPORT where PNCHIMPID <= @maxPunchID
if @@error <> 0
rollback Tran
else
commit tran
GO
************************************
If I create it without the "@maxPunchID int", I get the error that the variable needs to be declared. But when I declare it as I did above, I can't run the stored procedure without sending it a variable.
It doesn't matter what I send it, it still works because the "select @maxPunchID = Max(PNCHIMPID) from AC_PUNCHIMPORT" statement works as I expect it should.
Do I need to set the variable somehow? Or can I do something different that doesn't require declaring the variable at the beginning?
Thanks,
Bob
May 26, 2005 at 5:36 pm
Place the declare statement after the AS
CREATE PROCEDURE [dbo].[acsp_AC_PUNCHIMPORT_TO_AC_PUNCHIMPORT_TEMP]
AS
DECLARE @maxPunchID int
HTH Mike
May 26, 2005 at 9:04 pm
Bob,
By placing "@maxPunchID int" before the AS you created an input parameter to the stored procedure and as you discovered, were required to pass in a value for that parameter.
By DECLARing it after the AS you are just creating a variable within the procedure.
For what you are doing your variable should be declared withing the procedure (i.e. after the AS) and by default it will be initialized as NULL. Your SELECT statement will set it to the desired value if there are any rows in the table.
May 27, 2005 at 7:28 am
Would it be better to use a TRUNCATE statement on the table instead of using a DELETE statement? I've never timed it, but TRUNCATE seems to be faster to me.
May 27, 2005 at 7:31 am
Yes truncate is faster... but the problem here is that he doesn't want to delete data that has been inserted after he transfered the data to history.
May 27, 2005 at 7:35 am
INSERT TableB SELECT * FROM TableA
DELETE TableA WHERE identityCol <= (SELECT MAX(identityCol) FROM TableB)
TableB Should not have Identity Column.
Regards,
gova
May 27, 2005 at 7:37 am
Nice variant and one less step to it. But I wouldn't expect a big performance boost since the (SELECT MAX(identityCol) FROM TableA) in the first example will be lightning fast.
May 27, 2005 at 9:31 am
As a slight aside, I've had to do this before when the PK on my TableA could come into it in any order (not sequential). I just used the natural key and created a temp table:
INSERT TableB SELECT * FROM TableA WHERE TableA.ID IN (SELECT ID FROM #temp)
DELETE FROM TableA WHERE ID IN (SELECT ID FROM #temp)
Perhaps it may have been more efficient to build an IDENTITY column on my TableA and use the same solution here...
May 27, 2005 at 1:09 pm
What about setting up a trigger to perform the delete on a one by one basis? It would be transparent and would perform well...
Also...I know that most people avoid using triggers... but I'd never get a good explanation about why not to use'em... would any of you consider giving me (us) a helpfull hand on this?
Thanks!
Nicolas Donadio
Sr. Sotware Developer
DPS Automation
ARGENTINA
June 7, 2005 at 12:15 pm
If the "Every 5 minutes or so" is important, this suggestion won't work.
Create Table A (
pka int identity,
fld1 Varchar(32))
Create Table B (
pk int,
fld1 Varchar(32) )
Create Trigger xfer
on A
For Insert
As
Insert into B
Select pka, fld1 From inserted
go
Then whenever you want, clean up table A
delete from A where pka in (select pk from B )
-- test it out
insert into a Values ('1wahoo')
insert into a Values ('2wahoo')
insert into a Values ('3wahoo')
insert into a Values ('4wahoo')
insert into a Values ('5wahoo')
insert into a Values ('6wahoo')
insert into a Values ('7wahoo')
insert into a Values ('8wahoo')
insert into a Values ('9wahoo')
select * from b
delete from A where pka in (select pk from B )
Tom
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply