Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Duplicate Record Checking

Posted on 2013-10-28
6
Medium Priority
?
476 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

636 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