SQL QUERY - SQL 2005/2008 - check for existing records

Hi,

I have written the following code to compare a column in TableA which I use as a source for migration against a table look up in target database. If the values does not exist in lookup table, then add it, otherwise ignore.

I started to get error messages related to violation of primary key constraint ! which I don't get it, as the code checks only if value exists, then add it....


 
Insert into tragetDB.dbo.L_Building(building)
 Select building from sourceDB.dbo.L_Building
	
CREATE TABLE #L_Building
 ( building [nvarchar](25) PRIMARY KEY NOT NULL) 
  
 Insert into #L_Building (building) 
 Select building FROM tragetDB.dbo.L_Building

---lets say I go through a set of many employee tables
-- to extract building information one by one per table
-- hence the loop below...

SET @loop = @@ROWCOUNT 
WHILE (@loop <> 0)
BEGIN			

	SET @id = (SELECT autoid FROM #Employee WHERE autoid = @loop)
	IF (@id <> ''	Or @id <> null)
		BEGIN
		
		....

SELECT @strSql =  'IF EXISTS (	SELECT DISTINCT  (b.building)  AS building						
										FROM [' + @Employeesourcetable + ']  AS b
										WHERE NOT EXISTS(	SELECT 1 
															FROM  L_Building  as LB 
															WHERE LB.building =   (b.building) 
														)
										)																				 
								BEGIN													
INSERT INTO AI_DEV_Test.dbo.#L_building (b.building) 
FROM   TargetDB.dbo.[' + @Employeesourcetable + '] AS b 
WHERE NOT EXISTS(	SELECT 1 
			FROM  L_Building  as lb 
			WHERE lb.building =  (b.building) 
			)
		END'
EXECUTE sp_executesql @strsql																						
		END					
	SET @loop = @loop - 1
END

Open in new window


Thanks in advance
shmzAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
are you sure that thhe building column is your primary key
shmzAuthor Commented:
yes, 100%
each building has a unique number in this databbas
HainKurtSr. System AnalystCommented:
do you have any trigger? can you please explain what all code is doing by example?
what do you have initially and what do you want at the end... to me your code is over complicated and I think we can do the same thing with a simple select insert statement without any loop or any temp table...
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

BrandonGalderisiCommented:
Checking to ensure the record doesn't exist doesn't mean that you aren't attempting to insert duplicate records in one insert batch.  I suspect that is what your problem is.
shmzAuthor Commented:
Brandon, how is that possible? I am checking if that exists then don't insert again....

BrandonGalderisiCommented:
If this gives you two+ records, it won't matter that you have a "where not exists"

FROM   TargetDB.dbo.[' + @Employeesourcetable + '] AS b
shmzAuthor Commented:
Brandon,

what is happening is actually like this:

Insert into tragetDB.dbo.L_Building(building, AlternateName)
Select building, alternateName from sourceDB.dbo.L_Building

The problem is I have records like:

Building   alternateName
11XIS       11234
11XIS       11230

apparently the alternate name can change and as a result, I get more than 1 record returns...


How can I solve this problem? any way of getting the query fixed?
shmzAuthor Commented:
HainKurt,

I have tables X1, X2 as the source tables for data migration with columns: building and alternateName
In these tables the building column is not unique.

sample data:

building alternateName  size   unit     age
11XIS     11234              800   sqm    21
11XIS      11230             800   sqm    20

In the target database, I have  L_building table as the single lookup table. this table has the building as the primary key.
When ever I use the tables X1 and X2 to target table which is X,  Ineed first to check if there is any building in X1 or X2 which does not exists in L_building. If it does not exists then import that building into L_Building and then enter that info to TableX.

in the query above, I do not want to import 11XIS again into L_building. first one is enough and it should not complain about viloation of primary key,....


how can I fix the query?

thanks in advance
BrandonGalderisiCommented:
Then what you probably want is:


Insert into tragetDB.dbo.L_Building(building, AlternateName)
Select building, min(alternateName) from sourceDB.dbo.L_Building
group by building

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
shmzAuthor Commented:
Brandon,

does it solve the problem:
"If this gives you two+ records, it won't matter that you have a "where not exists"

WHERE NOT EXISTS(      
                        SELECT 1
                  FROM  L_Building  as lb
                  WHERE lb.building =  (b.building)
                  )

I actually changed the query to :

WHERE NOT EXISTS(      
                        SELECT *
                  FROM  L_Building  as lb
                  WHERE lb.building =  (b.building)
                  )
and only used BUILDING to insert and now it is not complaining about insert conflict.... or primary key violation,....
BrandonGalderisiCommented:
The where not exists won't matter because it's checked at the batch level and won't attempt to prevent you from inserting duplicates.  So you will probably need both the where not exists and the code I provided.
shmzAuthor Commented:
Thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.