Best solution to loop records and perform logic

I have a staging table which is loaded daily from a file.
I need to compare this staging table with present final table and
check for differences in columns and update final table and create an audit trail for changes
and generate a excel from sql server.

Is this possible? if yes can someone point me to guide/articles which would help me
LVL 1
rahulsuryaAsked:
Who is Participating?
 
lundnakConnect With a Mentor Commented:
This query should produce the same results as the except operator.
select stg.*,
       case when tbla.pk is null then 'INSERT'
            else 'UPDATE'
        end as CHANGE
from (select *, checksum(*) chksum
      from staging) stg
left outer join (select *, checksum(*) chksum
                 from table_a) tbla on stg.pk = tbla.pk
where stg.chksum <> isnull(tbla.chksum,0)

Open in new window

0
 
97WideGlideCommented:
It's doable but without knowing your database structure I don't know if your question is answerable.
Lot's of 'ands' in your question too.  Each one could be a separate question.
0
 
rahulsuryaAuthor Commented:
I load values from text file  to a staging table (with 36 columns)
then I have 2-3 tables where this data is finally gonna stored.

Ex: Table  A has all customers personal information
      Table B has all address for customers
      Table C has all account information

File has all the columns from these 3 tables in a single row which i upload to a table with 36 columns.

I want to loop this staging table check what data has changed update accordingly and assign and predefined event for tht update and store it in audit table with old/new value and then export this audit to excel report.

I want to complete all this in one process flow so my question included many ands I guess.;)
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
lundnakCommented:
This is a very big request.

If this is SQL 2005, you can use the Except operator to help find differences.
See below.  It is a rough script that may work.  I don't know how large the files or tables are.

-- This query finds rows in the staging table that are different or
-- not found in table_a
-- it builds a temp table with the changes
select fld1, fld2, fld3  --needs to match to each column in table_a
into #temp_a
from stagingtable
except
select *
from table_a
--repeat
 
--Find the rows that are new
--pk denotes column list that provide uniqueness
select *
into #new_records
from #tempa ta
where not exists (select 1
                  from table_a
                  where table_a.pk = ta.pk)
 
insert into audit_table
select *, getdate() inserteddate, 'Insert'
from #new_records
 
insert into table_a
select * from #new_records
 
--remove inserted records from temptable
--pk denotes column list that provide uniqueness
delete #tempa
from #tempa ta
join #new_records nr on nr.pk = ta.pk
 
--updated columns should be handled similarly
--the only records left in the temp table are those that were different
update table_a
   set col1 = tmpa.col1,
       col2 = tmpa.col2,
       ....
from table_a tbla
join #tempa tmpa on tbla.pk = tmpa.pk
 
insert into audit_table
select *, getdate() updatedate, 'update'
from #tempa
 
--next issue bcp command to send audit results file.

Open in new window

0
 
rahulsuryaAuthor Commented:
I'm using sql server 2000
Is there a  way do same thing in 2000.
Your idea and scripts looks perfect for me and good start. Thanks
0
 
lundnakCommented:
Too bad on SQL2000.  The except statement is powerful and easy.
select fld1, fld2, fld3  --needs to match to each column in table_a
into #temp_a
from stagingtable stg
where not exists (select 1 from table_a tbla
                  where stg.pk = tbla.pk)
or checksum(stg.*) <> checksum(tbla.*)
 
--checksum may work otherwise you will need to compare every relevant column, which is labor intensive.

Open in new window

0
 
rahulsuryaAuthor Commented:
checksum is throwing an excepting - Incorrect syntax near '*'
I guess i have to check each column now..
0
 
rahulsuryaAuthor Commented:
and my staging table has rows with changes only so i don't want to check which rows where changed
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.