Checking for duplicates on unique records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Checking for duplicates on unique records

Author

Topic

RynoB
Starting Member

2 Posts

Posted - 2010-02-21 : 06:22:25

Hi,

I'm trying to insert data from a table where I build company structures etc.

The problem I'm having is when I try to do a second insert on a self-referencing talbe where the LocationCode is the same as that of my first insert while trying to check for duplicates. The following sql will hopefully give you a better idea of what I'm trying to achieve.

Insert 1 - Inserting first level Location records


insert into Location 
(
	CompanyID, 
	ParentID, 
	LocationCode, 
	LocationName,  
	CanAssign
)

select distinct 1, NULL, ES.Locate, ES.Locate, 1
from ExternalSource ES
left join Location Loc on Loc.LocationCode = ES.Locate
where ES.Locate not in (select LocationCode from Location)

After the first insert everything is still fine as there are no parent records involved yet and duplicates is not yet an issue.

Insert 2 - Inserting second level Location records where it links to parent record on the same table


insert into Location 
(
	CompanyID, 
	ParentID, 
	LocationCode, 
	LocationName,  
	CanAssign
)

select 1, Loc2.LocationID, ES.Room, ES.Room, 1
from ExternalSource ES
left join Location Loc on Loc.LocationCode = ES.Room
left join Location Loc2 on Loc2.LocationCode = ES.Locate
where ES.Room not in (select LocationCode from Location)
group by Loc2.LocationID, ES.Room

The Problem:

 where ES.Room not in (select LocationCode from Location)

At the second insert I need to check for duplicate records when inserting records. However, in my first insert I have a LocationCode called 'TEST' and in my second insert I have multiple records which is also called 'TEST' which refers to something else when I join on my 'ExternalSource' table.

Thus, the where statement in my second insert will exclude all codes called 'TEST' since it already exists after my first insert.

How can I still insert my codes called 'TEST' in the second insert while still checking for duplicates?

NOTE: The only thing that makes the record unique is LocationCode, that is all I have to work with.

Any help will be highly appreciated.

Thanks.

R

subhash chandra
Starting Member

40 Posts

Posted - 2010-02-21 : 06:59:52

What I understood is that your second insert statement in not inserting 'TEST' code records and you want to isnert them. So what is issue in writing one more statement just after executing second statement as below:

insert into Location
(
CompanyID,
ParentID,
LocationCode,
LocationName,
CanAssign
)

select 1, Loc2.LocationID, ES.Room, ES.Room, 1
from ExternalSource ES
left join Location Loc on Loc.LocationCode = ES.Room
left join Location Loc2 on Loc2.LocationCode = ES.Locate
where ES.Room = 'TEST'
group by Loc2.LocationID, ES.Room

Regards,
Subhash Chandra
http://SQLReality.com/blog/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-21 : 08:55:12

can you provide some sample data? that should give us a fair idea of how your data is

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources