?
Solved

SQL Duplicate Record Checking

Posted on 2013-10-28
6
Medium Priority
?
475 Views
Last Modified: 2013-12-09
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
Comment
Question by:robthomas09
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 66

Accepted Solution

by:
Jim Horn earned 2000 total points
ID: 39606579
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
 
LVL 37

Expert Comment

by:momi_sabag
ID: 39606580
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
 
LVL 66

Expert Comment

by:Jim Horn
ID: 39606586
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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

Author Comment

by:robthomas09
ID: 39607780
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
 
LVL 32

Expert Comment

by:awking00
ID: 39611867
Are you looking to not insert if the entire row matches of if certain fields match?
0
 

Author Comment

by:robthomas09
ID: 39611948
Hi awking00,

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

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question