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....
Thanks in advance
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
Thanks in advance
Last Comment
Aneesh
are you sure that thhe building column is your primary key
ASKER
yes, 100%
each building has a unique number in this databbas
each building has a unique number in this databbas
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...
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...
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.
ASKER
Brandon, how is that possible? I am checking if that exists then don't insert again....
If this gives you two+ records, it won't matter that you have a "where not exists"
FROM TargetDB.dbo.[' + @Employeesourcetable + '] AS b
FROM TargetDB.dbo.[' + @Employeesourcetable + '] AS b
ASKER
Brandon,
what is happening is actually like this:
Insert into tragetDB.dbo.L_Building(bu
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?
what is happening is actually like this:
Insert into tragetDB.dbo.L_Building(bu
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?
ASKER
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
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
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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,....
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,....
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.
ASKER
Thanks
Microsoft SQL Server 2005
Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.
72K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
