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
Solved

SQL Duplicate Record Checking

Posted on 2013-10-28
6
468 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
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

839 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