Compare Tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Compare Tables

Author

Topic

stahorse
Yak Posting Veteran

86 Posts

Posted - 2013-10-10 : 05:43:06

Hi

I have this data first, before I explain my query and my issue:

create table staging(customer_id int not null unique, customer_name varchar(20), customer_lname varchar(20), [status] int)
insert into staging(customer_id, customer_name, customer_lname, [status])
values(1, 'James', 'Brown', 1), (2, 'Thabo', 'Kgosi', 1), (3, 'Horse', 'King', 0), (4, 'Tom', 'Smith', 1)

create table final(customer_id int not null unique, customer_name varchar(20), customer_lname varchar(20))
insert into final(customer_id, customer_name, customer_lname)
values(1, 'James', 'Brown'), (2, 'Thabo', 'Kgosi'), (3, 'Horse', 'King'), (4, 'Tom', 'Smith')

create table error(customer_id int not null unique,customer_lname varchar(20))

Let me explain my data first, I have Staging table, all the records gets validated and get signed a [status]. if a record fail verification it gets [status] = 1, otherwise it get passed staright to Final table.
Records in Staging will be validated again if they pass they go through Final table.

In my query below, I want to check data in Staging with status = 1, then check if that record is also there in Final, which it would mean it once failed verification. If I find that record I write it to Error table. I want to end up with all the records that once failed Validation.

insert into error
select fn.customer_id, fn.customer_name
from final fn
left join staging st
on fn.customer_id = st.customer_id
where in (select * from staging stg where stg.[status] = 1 and fn.customer_id = stg.customer_id)

I'm struggling with a concerpt but I think it should be along the code I wrote, please help.

stahorse
Yak Posting Veteran

86 Posts

Posted - 2013-10-10 : 05:49:45

I think I found the answer, it should be

insert into error
select fn.customer_id, fn.customer_name
from final fn
left join staging st
on fn.customer_id = st.customer_id
where exists (select * from staging stg where stg.[status] = 1 and fn.customer_id = stg.customer_id)

Correct me where you think could do better

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-10 : 05:53:23

as per your explanation, what you need is just this


insert into error
select fn.customer_id, fn.customer_name
from final fn
inner join staging st
on fn.customer_id = st.customer_id
where st.status = 1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources