Is there any way to restrict the data inserted into table if it is already present?

Question

I have an sp which when triggered, data will be inserted into the data table. If the data being inserted is already present in the table, I don't want the data to be inserted into the table. is there anyway i could use a logic to restrict the data into table.

my query is

declare @ident int =  IDENT_CURRENT( 'SADEV.RO_Transcript.ETQueueCtrl' )

insert into SADEV.RO_Transcript.ETQueueCtrl ([STU_ID],[STU_ORD_SEQ_NUM],[CreatedDate],[LastUpdatedDate],[ETQueueCtrlStatusCd],[ErrorFl])
select STU_ID
      ,STU_ORD_SEQ_NUM
      ,getdate()
      ,getdate()
      ,[ETQueueCtrlStatusCd] = 'N'
      ,'N'

from srdb_sr2_qa.dbo.SR0ROT rt
where STU_ORD_TYP_CD = 'A'
and ORD_DLVY_MTHD_CD = 'ET'
and STU_ORD_STAT_CD = 'C'

--and convert(varchar(1),STU_ORD_XPDT_FL) = @stu_ord_xpdt_fl
and case when @stu_ord_xpdt_fl = 'y' then GETDATE()
                            else case when ORD_PEND_INST_CD = '' then  STU_ORD_SBM_DT+ DATEADD (mi,480,STU_ORD_SBM_TM) 
                                    else  LAST_UPD_DT+ DATEADD (mi,480,LAST_UPD_TM) 
                                 end 
         end <= GETDATE()

select et.ETQueueCtrlID,
       ro.STU_ID,
       ro.STU_ORD_SEQ_NUM,
       ty.CAREER_CD,
       ty.CAREER_SUFX_CD


from SADEV.RO_Transcript.ETQueueCtrl et join srdb_sr2_qa.dbo.SR0ROT ro on et.STU_ID = ro.STU_ID 
                                                      and et.STU_ORD_SEQ_NUM = ro.STU_ORD_SEQ_NUM
                                   left join srdb_sr2_qa.dbo.SR0TYT ty on ro.STU_ID = ty.STU_ID
                                   where et.ETQueueCtrlID > @ident

Are you allowed to put a "Constraint" on the table, or are you looking for a different approach? — tgolisch, Jan 15 '16 at 18:17
in that where, can't you just include ETQueueCtrlID not in (select ETQueueCtrlID from SADEV.RO_Transcript.ETQueueCtrl)? — David Manheim, Jan 15 '16 at 18:37
@sam assuming et.ETQueueCtrlID is not available, what combination of fields would we need to retrieve a unique row? — Fuzzy, Jan 15 '16 at 19:03
Possible duplicate of [SQL Server insert if not exists best practice](http://stackoverflow.com/questions/5288283/sql-server-insert-if-not-exists-best-practice) — Tab Alleman, Jan 15 '16 at 20:49

score 1 · Answer 1 · answered Jan 15 '16 at 21:59

Could you add something like the below into the WHERE clause for the Insert?

AND 
NOT EXISTS
    (SELECT *
    FROM sadev.ro_transcript.etqueuectrl AS trg
    WHERE
        rt.stu_id = trg.stu_id
        AND
        rt.stu_ord_seq_num = trg.stu_ord_seq_num)

You can adjust the above to filter out those data columns that shouldn't be duplicated

Is there any way to restrict the data inserted into table if it is already present?

1 Answers1