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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

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
lundnakCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.