CODEWITHSUNDEEP
sqlt-sql
SeanFlynn
SeanFlynn

Reputation: 453

SQL Server 2005 Unique Index Insert from Staging Table with Where Not In

I have a unique index set up in tblCombined. It is set up on the fields called cyDate, dtlNr, and seq, in that order.

I have another table - a staging table - called tblDailyResults. It has the exact same fields as tblCombined, but no index. Each day, I receive records into this staging table from a feed.

What I'd like to do is setup a "catch" if you will, so that if a duplicate record that violates the unique index, it won't error out - rather, it will just not be inserted; it will remain in the staging table (I can then send out an alert noting such and manage it from there).

This is what I've tried:

   Insert Into tblCombined
    ( 
        cyDate      
        ,dtlNr
        ,seq
        ,chCode
        ,opCode
        ,nrCode

    )

    Select
        cyDate      
        ,dtlNr
        ,seq
        ,chCode
        ,opCode
        ,nrCode

    From tblDailyResults

    Where Not Exists (Select cyDate ,dtlNr ,seq From tblCombined)

But, this doesn't seem to be working. I tested out a couple of records - changing the fields from what was already inserted, and it still excludes the non-duplicate records. I admit I'm new to using "not exists" so perhaps I'm not using it correctly.

I also tried Where Not In, but that doesn't seem to work for multiple columns.

Any suggestions is appreciated. Thanks!

Upvotes: 1

Views: 261

Answers (2)

Joe
Joe

Reputation: 1

Here is an alternate way:

Insert Into tblCombined
( 
    cyDate      
    ,dtlNr
    ,seq
    ,chCode
    ,opCode
    ,nrCode
)

Select distinct 
    a.cyDate      
    ,a.dtlNr
    ,a.seq
    ,a.chCode
    ,a.opCode
    ,a.nrCode
From tblDailyResults a
left join tblCombined b on a.cyDate = b.cyDate
                        and a.dtlNr= b.dtlNr
                        and a.seq= b.seq
                        and a.chCode= b.chCode
                        and a.opCode= b.opCode
                        and a.nrCode= b.nrCode
where b.cydate is null

Upvotes: 0

Rodolfo
Rodolfo

Reputation: 4183

you need a where in the not exists part too, like ...

where not exists (select 1 from tblcombined 
  where tblcombined.cydate = tbldailyresults.cydate 
  and tblcombined.dtlnr = tbldailyresults.dtlnr 
  and tblcombined.seq = tbldailyresults.seq)

Upvotes: 2

Related Questions