Solved

SQL Inserting Rows Preventing Duplicate Entries

Posted on 2014-03-13
2
1,222 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:Scott Pletcher
Scott Pletcher 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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

863 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

23 Experts available now in Live!

Get 1:1 Help Now