[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 354
  • Last Modified:

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
0
rahulsurya
Asked:
rahulsurya
  • 4
  • 3
1 Solution
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now