[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 479
  • Last Modified:

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

0
robthomas09
Asked:
robthomas09
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
The below INSERT will insert only those rows that do not currently exist in MTS_Fax_Config_Saved_ (Replace the JOIN ... ON and WHERE block, ID with whatever column is used to relate these two tables)
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, p.description 
from #providers p
   LEFT JOIN MTS_Fax_Config_Saved_ mtsfcs ON p.ID = mtsfcs.ID
WHERE mtsfcs.ID IS NULL

Open in new window

Which begs the question ... what do you want to do when the rows match?
0
 
momi_sabagCommented:
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
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
robthomas09Author Commented:
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.
0
 
awking00Commented:
Are you looking to not insert if the entire row matches of if certain fields match?
0
 
robthomas09Author Commented:
Hi awking00,

I am looking to not insert if the entire row matches
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now