Hi All,
Need some help in writing tsql query using which I should be able to tell what all records in stg table are already existins ones when compared with main table records.
I have a unique constraint is defined on 2 columns on main table.
Basically, I want to take each record from stg table and compare the unique constraint defined column combination in the main table and list out how many times that record exists in stg tbl.
Q1) I want to know the repeated rows along with repeated count of staging tbl rows
Q2) Identify duplicate rows and move those records into error tbl
Can anyone please help.
Sample example :
====================
IF OBJECT_ID('dbo.stg_tbl', 'U') IS NOT NULL
drop table stg_tbl
IF OBJECT_ID('dbo.main_tbl', 'U') IS NOT NULL
drop table main_tbl
create table stg_tbl
(c1 int,
c2 varchar(10),
c3 varchar(10)
)
insert into stg_tbl
select 101,'A','aaaa'
union all
select 101,'A','aaaa'
union all
select 102,'B','bbbb'
union all
select 102,'B','bbbb'
union all
select 102,'B','bbbb'
union all
select 103,'C','cccc'
union all
select 103,'C','cccc'
union all
select 104,'D','dddd'
union all
select 105,'E','eeee'
union all
select 106,'F','ffff'
union all
select 107,'G','gggg'
union all
select 108,'H','hhhh'
create table main_tbl
(c1 int,
c2 varchar(10),
c3 varchar(10)
)
ALTER TABLE main_tbl ADD CONSTRAINT Uniq_main_tbl_c1c2 UNIQUE(c1,c2);
insert into main_tbl
select 101,'A','aaaa'
union all
select 102,'B','bbbb'
union all
select 103,'C','cccc'
Excepted output
===================
c1 c2 c3 cnt
101 A aaaa 2
102 B bbbb 3
103 C cccc 2
104 D dddd 0
105 E eeee 0
106 F ffff 0
107 G gggg 0
108 H hhhh 0
Thanks,
Sam
October 29, 2019 at 5:13 pm
Hello there
try
SELECT c1,C2,MIN(C3)C3,COUNT(*) SAY FROM stg_tbl AS G
WHERE exists (SELECT * FROM main_tbl AS F WHERE F.c1=G.c1 AND F.c2=G.c2 AND F.c3=(G.C3))
GROUP BY c1,C2
UNION ALL
SELECT c1,C2,MIN(C3)C3,0 FROM stg_tbl AS G
WHERE NOT exists (SELECT * FROM main_tbl AS F WHERE F.c1=G.c1 AND F.c2=G.c2 AND F.c3=(G.C3))
GROUP BY c1,C2
Allah bize yeter, O ne güzel vekildir.
vedatozer@outlook.com
October 29, 2019 at 5:55 pm
select s.c1,s.c2,s.c3, COUNT(*)*IIF(m.c1 IS NULL,0,1) cnt
from stg_tbl s
left join main_tbl m
on m.c1=s.c1
and m.c2=s.c2
and m.c3=s.c3
group by s.c1,s.c2,s.c3,m.c1
select s.c1,s.c2,s.c3, COUNT(*)*IIF(m.c1 IS NULL,0,1) cnt
from stg_tbl s
left join main_tbl m
on m.c1=s.c1
and m.c2=s.c2
and m.c3=s.c3
group by s.c1,s.c2,s.c3,m.c1
Nice, but I think you can simplify it to:
select s.c1,s.c2,s.c3,
COUNT(m.c1) AS count
from stg_tbl s
left join main_tbl m
on m.c1=s.c1
and m.c2=s.c2
and m.c3=s.c3
group by s.c1,s.c2,s.c3
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply