• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 832
  • Last Modified:

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
0
shmz
Asked:
shmz
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
are you sure that thhe building column is your primary key
0
 
shmzAuthor Commented:
yes, 100%
each building has a unique number in this databbas
0
 
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...
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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.
0
 
shmzAuthor Commented:
Brandon, how is that possible? I am checking if that exists then don't insert again....

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

FROM   TargetDB.dbo.[' + @Employeesourcetable + '] AS b
0
 
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?
0
 
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
0
 
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
0
 
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,....
0
 
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.
0
 
shmzAuthor Commented:
Thanks
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now