August 7, 2019 at 10:19 pm
Hi,
I'm working on data archiving to Archive database from source database. I have to insert data from source database table to archive database and then delete from source. But I want to perform a check by getting no.of rows inserted is equal to no.of rows that are selected to delete. Table1 is parent table and Table2 is child table. Below is the stored procedure I'm using and running that from a SQL agent job.
Procedure:
Create PROC [dbo].[Archive_testcounts]
(
@CutOffPeriod CreateDateTime = NULL
)
AS
BEGIN
SET NOCOUNT ON
IF @CutOffPeriod IS NULL
BEGIN
SET @CutOffPeriod = DATEADD(mm, -6, cast(getdate() As Date))
END
ELSE
BEGIN
IF @CutOffPeriod > DATEADD(mm, -3, cast(getdate() As Date))
BEGIN
RAISERROR ('Cannot delete records from last 3 month', 16, 1)
RETURN -1
END
END
BEGIN TRAN
declare @Delete1 int,@delete2 int, @Insert1 int, @insert2 int
--Copy data from SourceDB tables to archive DB tables:
--1. Table1
set @insert1 =(select count (*) from SourceDB.Table1 o
where ID in
(
select distinct ID from SourceDB.dbo.Table2 where CreateDateTime<@CutOffPeriod)
and ID not in (
select ID
from Archive.dbo.Table1
))
print @insert1
insert into archive.dbo.table1
select T1.*,getdate() as loaddate from SourceDB.dbo.table1 T1
where ID in
(
select distinct ID from SourceDB.dbo.Table2 where CreateDateTime<@CutOffPeriod)
and ID not in (
select ID
from Archive.dbo.Table1
)
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while copying data to Archive.table1', 16, 1)
RETURN -1
END
--2. Table2
set @insert2=(select count (*) from SourceDB.dbo.table2 where CreateDateTime<@CutOffPeriod)
print @insert2
insert into archive.dbo.table2
select T2.*,getdate() as loaddate from SourceDB.dbo.Table2 T2 where CreateDateTime<@CutOffPeriod
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while copying data to Archive.Table2', 16, 1)
RETURN -1
END
set @delete1=(select count(*) from SourceDB.dbo.Table2 where CreateDateTime<@CutOffPeriod)
print @delete1
if @insert1=@delete1
delete from SourceDB.dbo.Table2 where CreateDateTime<@CutOffPeriod
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from SourceDB.dbo.Table2', 16, 1)
RETURN -1
END
set @delete2=(select count(*) from SourceDB.dbo.Table1
where ID in
(
select distinct ID from Archive.dbo.Table2 where cast(loaddate as date)=cast(getdate() as date)))
print @delete2
if @insert2=@delete2
delete from SourceDB.dbo.Table1
where ID in
(
--select ID from SorceDB.dbo.Table2 where CreateDateTime<@CutOffPeriod)
select distinct ID from Archive.dbo.Table2 where cast(loaddate as date)=cast(getdate() as date)
)
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from SourceDB.dbo.Table1', 16, 1)
RETURN -1
END
IF @@TRANCOUNT > 0
BEGIN
COMMIT TRAN
RETURN 0
END
END
But the job is writing to the output file as below
Job 'Archive' : Step 1, 'archive' : Began Executing 2019-08-07 13:28:34
4 [SQLSTATE 01000]
156 [SQLSTATE 01000]
156 [SQLSTATE 01000]
4 [SQLSTATE 01000]
How to avoid [SQLSTATE 01000] in output file?
Please let me know if I'm doing anything wrong.
August 7, 2019 at 10:32 pm
Why the DISTINCT?
where ID in
(
select distinct ID from SourceDB.dbo.Table2 where CreateDateTime<@CutOffPeriod)
Doesn't matter if there are duplicates in the list you're matching to. DISTINCT is expensive. Use it only when you need to.
August 7, 2019 at 11:03 pm
[SQLSTATE 01000] is normal to see with print statements executed through SQL Agent. You can ignore them.
Sue
August 8, 2019 at 11:30 pm
can we suppress [SQLSTATE 01000] messages writing to log file?
August 9, 2019 at 1:09 am
When you insert the rows you can OUTPUT the Id's into a temporary table. Then delete the Ids from the source table that are in the temporary table.
Also, you should have a try/catch block around the entire set of queries. In the catch you should rollback and on success commit.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply