How to make sure that all the rows are inserted into datamodel and then truncate staging table

Question

How to make sure that all the rows are inserted into datamodel and then truncate staging table

Confusing Queries

Ten Centuries

Points: 1137
More actions
May 8, 2014 at 8:35 pm

#286595

Hi,
In my ETL job I would like to truncate stg table but before truncating stging table, I want to make sure that all the records are inserted in the data model.
The sample is as below
create table #stg (
CreateID int,
Name nvarchar(10)
)
insert into #stg
select 1, 'a' union all
select 2, 'b' union all
select 3, 'c' union all
select 4, 'd'
go
create table #Approve1 (
CreateID int,
Name nvarchar(10)
)
insert into #Approve1
select 1, 'a' union all
select 4, 'd'
create table #Approve2 (
CreateID int,
Name nvarchar(10)
)
insert into #Approve2
select 3, 'c'
create table #Reject (
CreateID int,
Name nvarchar(10)
)
insert into #Reject
select 2, 'b'
select * from #stg
select * from #Approve1
select * from #Approve2
select * from #Reject
How can I check among these tables and make sure that all values are loaded into the data model and the staging table can be truncated.
Thanks

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

HanShi SSC-Dedicated Points: 33507 More actions · Answer 1

The most easy way is to compare the number of records of your staging table with the total number of records of the ETL tables.

IF (select COUNT(*) from #stg)

=

(select COUNT(*) from (select * from #Approve1

union all

select * from #Approve2

union all

select * from #Reject

) sub

)

BEGIN

truncate table #stg

select 'All records from #stg processed; table truncated.'

END

ELSE

BEGIN

select 'Not all records from #stg are processed.'

END

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

What are you using to perform this ETL job? Is it just TSQL or are you using SSIS or some other tool?

"In my ETL job I would like to truncate stg table but before truncating stging table, I want to make sure that all the records are inserted in the data model."

Think of this another way: if the step which inserts rows into the data model succeeds, then truncate the staging table and continue. If it fails, then do something different.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden