Solved

SQL Duplicate Record Checking

Posted on 2013-10-28
6
461 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
6 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 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 65

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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 31

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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

707 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now