?
Solved

SQL Inserting Rows Preventing Duplicate Entries

Posted on 2014-03-13
2
Medium Priority
?
1,360 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
[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
2 Comments
 
LVL 69

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

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
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…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

770 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