asked on

SQL Duplicate Record Checking

Hello Experts,

I have a stored procedure that is currently writing values to a table. I am looking for a way to check and see if the record(s) that is/are being written to the table exist before they are written. I've attached the stored procedure below. Any help would be fantastic.

ALTER procedure [dbo].[MTS_Load_Config_Saved_All_Providers](
	@practice_id varchar(4),
	@location varchar (40),
	@document varchar(40),
	@practice varchar(40))
as

select description, pm.provider_id into #providers
from provider_mstr pm
join provider_practice_mstr ppm on pm.provider_id=ppm.provider_id
where ppm.attending_ind='Y' and pm.delete_ind='N' and ppm.delete_ind='N' and ppm.practice_id = @practice_id
order by description

insert into MTS_Fax_Config_Saved_ (seq_no, practice_id, created_by, modified_by, txt_document_description, txt_practice_name, txt_location_name, txt_provider_name)
select newid(), @practice_id, 0, 0, @document, @practice, @location, description from #providers

Open in new window

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

momi_sabag

what do you want to do if the record already exists? update it? if so, you should either split your command into two commands - one that inserts and one that updates or use the merge command

Jim Horn

Btw, looking at the SELECT ... line, it looks like a row for EVERY row in #providers, with the description from that table, and a bunch of columns all a single variable/zero/newid.

Verify that that is correct.

robthomas09

ASKER

Hello Jimhorn,

If the row already exists i don't want it to insert to the table. Your description of the SELECT line is correct. Can I maybe use something along these lines?

insert into MTS_Fax_Config_Saved (seq_no, practice_id, created_by, modified_by, txt_document_description, txt_practice_name, txt_location_name, txt_provider_name)
select newid(), @practice_id, 0, 0, @document, @practice, @location, description from #providers
--where newid(), @practice_id, 0, 0, @document, @practice, @location, description from #providers not in (select values from table)

Open in new window

Thanks for the help.

awking00

Are you looking to not insert if the entire row matches of if certain fields match?

robthomas09

ASKER

Hi awking00,

I am looking to not insert if the entire row matches