Solved

SQL Inserting Rows Preventing Duplicate Entries

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

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