Solved

SQL Inserting Rows Preventing Duplicate Entries

Posted on 2014-03-13
2
1,196 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 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 100 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 400 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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

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.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

743 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

12 Experts available now in Live!

Get 1:1 Help Now