Loading data using cursor inside of stored proc

i looking method validate number of records loaded production table staging table. loading records using stored procedure , cursor. stored procedure pulls records staging table , loads them production table. after stored proc runs want validate number of rows loaded production table matches number of rows in staging table. in advance help!!

hello jc524400,

here sample code , how can check compare databetween 2 tables.

create table #staging( id int, name varchar(20))  go    insert #staging values(1,'oldone')  go  insert #staging values(2,'oldtwo')  go  insert #staging values(2,'oldthree')    --creating new table  create table #prod(id int, name varchar(20))  go  insert #prod values(1,'oldone')  go  insert #prod values(2,'oldtwo')  ======================================  --one way  --will give records exits in staging not in producton, can take not exits exists , show records exits in both tables.    select id,name #staging j  not exists(select * #prod n  n.id=j.id  , n.name=j.name)  --results  --2 oldthree  =======================================  --second way    --you no records if matching, if staging has more   records not exits in production.    select id,name #staging  except  select id,name #prod  ==========================================  --third way    select t.id,t.name #staging t  full outer join #prod  on t.id=a.id  , t.name=a.name   a.id null    ===========================  --fourth way    --intersect give records exist in both tables.    select id,name #staging  intersect   select id,name #prod  

you can use ssis , if want confirm how many records match in both tables.

steps:

1--drag data flow task

2--drage oledb data source , make connection production table

3--drag lookup , make connection staging table, map production table columns staging columns

4-- test, drag datareader oledb destination.... connect green arrow datareader destination... , if want see records not match , configure lookup , redirect error records , connect red arrow datareader destination. dont have configure datareaderdestination want make sure , how many records match , how many not...

5--run package , can visualize flow, , can see count.

good luck!!

SQL Server  >  SQL Server Integration Services