?
Solved

SQL Inserting Rows Preventing Duplicate Entries

Posted on 2014-03-13
2
Medium Priority
?
1,444 Views
Last Modified: 2014-04-03
Hello Experts,

I am developing the following script to insert rows from one table to another using a temp table.

select * from NGTest.dbo.ngkbm_custom_dbp_item_dtl_ where txt_dbpicklist_name = 'My Phrases'
and create_timestamp between DATEADD(MONTH,-6,GETDATE()) and GETDATE() order by create_timestamp desc

insert into NGProd.dbo.ngkbm_custom_dbp_item_dtl_
(site_id,
seq_no,
created_by,
modified_by,
chk_edit_list_item,
chk_edit_location,
chk_edit_practice_id,
chk_edit_providername,
chk_edit_qualifier1,
chk_edit_qualifier2,
kbm_ind,
txt_dbpicklist_name,
txt_edit_mode,
txt_list_item,
txt_location_name,
txt_practice_id,
txt_provider_name,
txt_qualifier_1,
txt_qualifier_2)

select(site_id,
NEWID(),
created_by,
modified_by,
chk_edit_list_item,
chk_edit_location,
chk_edit_practice_id,
chk_edit_providername,
chk_edit_qualifier1,
chk_edit_qualifier2,
kbm_ind,
txt_dbpicklist_name,
txt_edit_mode,
txt_list_item,
txt_location_name,
txt_practice_id,
txt_provider_name,
txt_qualifier_1,
txt_qualifier_2
)

from #temp_table

where not exists (select txt_list_item, txt_provider_name from ngkbm_custom_dbp_item_dtl_ 
where ngkbm_custom_dbp_item_dtl_.txt_list_item = #temp_table.txt_list_item 
	and nkgbm_custom_dbp_item_dtl_.txt_provider_name = #temp_table.txt_provider_name)

Open in new window


On the insert from the temp table i am trying to prevent duplicate entries on the txt_provider_name and txt_list_item columns. I was hoping to use something similar to the where not exists clause to accomplish this.

where not exists (select txt_list_item, txt_provider_name from ngkbm_custom_dbp_item_dtl_ 
where ngkbm_custom_dbp_item_dtl_.txt_list_item = #temp_table.txt_list_item 
	and nkgbm_custom_dbp_item_dtl_.txt_provider_name = #temp_table.txt_provider_name

Open in new window


 Can this be used?
0
Comment
Question by:robthomas09
2 Comments
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 400 total points
ID: 39927797
That should do it, looks OK to me.

Just get rid of the open paren after select and the close paren before from.
0
 
LVL 11

Accepted Solution

by:
John_Vidmar earned 1600 total points
ID: 39927907
Another technique, instead of using a correlated subquery:
insert	ngkbm_custom_dbp_item_dtl
(	site_id
,	seq_no
,	created_by
,	modified_by
,	chk_edit_list_item
,	chk_edit_location
,	chk_edit_practice_id
,	chk_edit_providername
,	chk_edit_qualifier1
,	chk_edit_qualifier2
,	kbm_ind
,	txt_dbpicklist_name
,	txt_edit_mode
,	txt_list_item
,	txt_location_name
,	txt_practice_id
,	txt_provider_name
,	txt_qualifier_1
,	txt_qualifier_2
)
select	a.site_id
,	NEWID()
,	a.created_by
,	a.modified_by
,	a.chk_edit_list_item
,	a.chk_edit_location
,	a.chk_edit_practice_id
,	a.chk_edit_providername
,	a.chk_edit_qualifier1
,	a.chk_edit_qualifier2
,	a.kbm_ind
,	a.txt_dbpicklist_name
,	a.txt_edit_mode
,	a.txt_list_item
,	a.txt_location_name
,	a.txt_practice_id
,	a.txt_provider_name
,	a.txt_qualifier_1
,	a.txt_qualifier_2
from	#temp_table			a
left
join	ngkbm_custom_dbp_item_dtl	b	on	a.txt_list_item = b.txt_list_item
						and	a.txt_provider_name = b.txt_provider_name
where	b.txt_list_item IS NULL
                                  

Open in new window

0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
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.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

840 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