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
Who is Participating?
lundnakConnect With a Mentor Commented:
This query should produce the same results as the except operator.
select stg.*,
       case when 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 =
where stg.chksum <> isnull(tbla.chksum,0)

Open in new window

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.
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.;)
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.

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
select *
from table_a
--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 =
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 =
--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 =
insert into audit_table
select *, getdate() updatedate, 'update'
from #tempa
--next issue bcp command to send audit results file.

Open in new window

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
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 =
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

rahulsuryaAuthor Commented:
checksum is throwing an excepting - Incorrect syntax near '*'
I guess i have to check each column now..
rahulsuryaAuthor Commented:
and my staging table has rows with changes only so i don't want to check which rows where changed
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.