seeking for tsql help

  • 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

  • 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.

    vedatoozer@gmail.com

  • 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
  • Jonathan AC Roberts wrote:

    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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply