April 5, 2013 at 6:39 am
I created a table using the statement below in one of my databases (database1). The table is to be updated with information from a table in another database (database2) at a regular interval using an Insert Into statement. When creating the table, in addition to the fields I specified, it created a foreign key called PK_Test_ELE_761abed01e1455b5. I got my intial set of data into the table without a problem using the insert statement below without the where clause, but after that, every time I try to update it using the where clause I would get the following error message: "Violation of PRIMARY KEY constraint 'PK__ELEVATIONS__7B5130AA'. Cannot insert duplicate key in object 'Test_ELEVATIONS'."
To get around it for testing in Beta I just deleted the key from the table. Before moving to prod, I wanted to get some additional information. Why was the key created and whats the best way to handle this? Thank in advance for any input.
CREATE TABLE Test_ELEVATIONS
(ProjectID nvarchar(30) not null Primary Key,
ParcelID nvarchar(24) null,
Ukey nvarchar(15) null,
Location nvarchar(75) not null,
AppStatus nvarchar(30) null,
AppStatusDate datetime null,
Type nvarchar(30) null,
Category nvarchar(30) null,
Community_Number nvarchar(30) null,
Panel nvarchar(30) null,
Datum nvarchar(30) null,
Base_Flood_Elevation nvarchar(30) null,
Lowest_Adjacent_Grade nvarchar(30) null,
Lowest_Machinery_Elevation nvarchar(30) null,
Date_of_Firm nvarchar(30) null,
Firm_Zone nvarchar(30) null,
License_Number nvarchar(30) null,
Floor_Elevation nvarchar(30) null
)
Use Database2
Insert Into database1.Test_ELEVATIONS
(
ProjectID,
ParcelID,
UKEY,
Location,
AppStatus,
AppStatusDate,
Type,
Category,
Community_Number ,
Panel ,
Datum,
Base_Flood_Elevation,
Lowest_Adjacent_Grade,
Lowest_Machinery_Elevation,
Date_of_Firm,
Firm_Zone,
License_Number,
Floor_Elevation)
Select .......
From ....
Where ProjectID NOT IN (SELECT PROJECTID FROM database1.Test_ELEVATIONS)
April 5, 2013 at 6:49 am
The table you're selecting from has duplicate project ids. Try
SELECT PROJECTID,COUNT(*) FROM BLAH GROUP BY PROJECTID HAVING COUNT(*) > 1
To identify the problematic row(s).
April 5, 2013 at 6:58 am
The projectID is a primary key in both the source and target . I double checked using your query and it produced no results.
When I delete the foreign key from the target table while keeping ProjectID defined as a primary key , the update statement works.
April 5, 2013 at 7:03 am
What do you get when you do this?
select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where CONSTRAINT_NAME = 'PK__ELEVATIONS__7B5130AA'
April 5, 2013 at 7:12 am
CONSTRAINT_CATALOGdatabase1
CONSTRAINT_SCHEMAdbo
CONSTRAINT_NAMEPK__Test_ELE__761ABED021E4E699
TABLE_CATALOGdatabase1
TABLE_SCHEMAdbo
TABLE_NAMETest_ELEVATIONS
COLUMN_NAMEProjectID
ORDINAL_POSITION1
(*I dropped and recreated the table and when I did the numbers on the constrain_name changed, which is why its not matching my original post)
And thank you for the quick replies
April 5, 2013 at 8:02 am
Apparently something in your select is generating duplicate rows. Try it without the INSERT and the problem should become evident. As a quick check, try adding a DISTINCT. The varchar(30) primary key is really a no-no, though. it will make all indexes on the table bloated.
April 5, 2013 at 8:06 am
Can you post the whole query please?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 5, 2013 at 9:08 am
OK, but be gentel, I'm still new to SQL 🙂
As I mentioned before, Field1 AKA ProjectID is a primary key in the source. This query does not produce any duplicate Project ID's. If I run it, then go to test_elevations and randomly delete a few records, then run it again, I get the referenced error. Wouldn't the where clause "P.Field1 NOT IN (SELECT PROJECTID FROM Test_Elevations)" eliminate any duplicated if there were any?
Thanks
------
Insert Into Test_Elevations
(
ProjectID,
ParcelID,
UKEY,
Location,
AppStatus,
AppStatusDate,
Type,
Category,
Community_Number ,
Panel ,
Datum,
Base_Flood_Elevation,
Lowest_Adjacent_Grade,
Lowest_Machinery_Elevation,
Date_of_Firm,
Firm_Zone,
License_Number,
Floor_Elevation)
select
Field1 AS ProjectID,
Field2 AS ParcelID,
UKEY,
Full_Address as Location,
Field3 as AppStatus,
Field4 as AppStatusDate,
Field5 as [Type],
Field6 AS Category,
[Community Number] AS Community_Number,
[Panel] AS Panel,
[Datum]AS Datum,
[Base Flood Elevation]AS Base_Flood_Elevation,
[Lowest Adjacent Grade]AS Lowest_Adjacent_Grade,
[Lowest Machinery Elevation] AS Lowest_Machinery_Elevation,
[Date of Firm] AS Date_of_Firm,
[Firm Zone] AS Firm_Zone,
[License Number] AS License_Number,
[Floor Elevation] AS Floor_Elevation
FROM
(SELECT
P.Field1,
P.ID1,
P.ID2,
P.ID3,
a.Address1,
DBO.FN_GET_ADDRESS_INFO('BOCC', P.ID1, P.ID2, P.ID3, 'Y', 'FullAddr_Line')AS Full_Address,
P.Field3,
P.Field4,
P.File_Date,
P.spc,
P.Field5,
P.Field6,
(SELECT TOP 1 Value1 FROM Table1 AS O WHERE P.ID1=O.ID1 AND P.ID2=O.ID2 AND P.ID3=O.ID3 AND P.spc=O.spc AND A.ANBR=O.APNBR AND O.Name='ADDRESSKEY') AS UKEY,
(Select TOP 1 Field2 FROM Table2 AS R WHERE P.ID1=R.ID1 AND P.ID2=R.ID2 AND P.ID3=R.ID3 AND P.spc=R.spc) AS Field2,
C.DESC1,
C.COMMENT1,
C.TYPE1
FROM
Table3 AS P JOIN Table4 AS A ON P.ID1=A.ID1 AND P.ID2=A.ID2 AND P.ID3=A.ID3 AND P.spc=A.spc
INNER JOIN Table5 AS C ON P.ID1=C.ID1 AND P.ID2=A.ID2 AND P.ID3=C.ID3 AND P.spc=C.spc
WHERE C.TYPE1='FEC'
AND P.spc='BOCC'
AND ((P.Field5 ='Com' and P.Field6 IN ('Misc', 'Bl'))
OR (P.Field5 ='Residential' and P.Field6 IN ('SF', 'MH')))
AND COMMENT1 IS NOT NULL
AND P.Field3 <> 'Void'
AND P.Field1 NOT IN (SELECT PROJECTID FROM Test_Elevations)
)
AS TableToBePivoted
PIVOT
(
MAX(COMMENT1)
FOR desc1 IN ([Community Number], [Panel], [Datum], [Base Flood Elevation], [Lowest Adjacent Grade], [Lowest Machinery Elevation], [Date of Firm], [Firm Zone], [License Number], [Floor Elevation])
) AS PivotedTable
April 5, 2013 at 2:18 pm
But that query doesn't have
where not exists(select 1 from Test_Elevations where productid = field1)
like the original did.
oops, I guess it did.
Maybe if you put it at the end?
April 8, 2013 at 7:51 am
-- run the results into a #temp table and check for dupes:
select
Field1 AS ProjectID, --
Field2 AS ParcelID,
UKEY,
Full_Address as Location,
Field3 as AppStatus,
Field4 as AppStatusDate,
Field5 as [Type],
Field6 AS Category,
[Community Number] AS Community_Number,
[Panel] AS Panel,
[Datum]AS Datum,
[Base Flood Elevation]AS Base_Flood_Elevation,
[Lowest Adjacent Grade]AS Lowest_Adjacent_Grade,
[Lowest Machinery Elevation] AS Lowest_Machinery_Elevation,
[Date of Firm] AS Date_of_Firm,
[Firm Zone] AS Firm_Zone,
[License Number] AS License_Number,
[Floor Elevation] AS Floor_Elevation
INTO #PivotedSourceTable -- #temp table
FROM (
SELECT
P.Field1, --
P.ID1,
P.ID2,
P.ID3,
a.Address1,
DBO.FN_GET_ADDRESS_INFO('BOCC', P.ID1, P.ID2, P.ID3, 'Y', 'FullAddr_Line')AS Full_Address,
P.Field3,
P.Field4,
P.File_Date,
P.spc,
P.Field5,
P.Field6,
(SELECT TOP 1 Value1 FROM Table1 AS O WHERE P.ID1=O.ID1 AND P.ID2=O.ID2 AND P.ID3=O.ID3 AND P.spc=O.spc AND A.ANBR=O.APNBR AND O.Name='ADDRESSKEY') AS UKEY,
(Select TOP 1 Field2 FROM Table2 AS R WHERE P.ID1=R.ID1 AND P.ID2=R.ID2 AND P.ID3=R.ID3 AND P.spc=R.spc) AS Field2,
C.DESC1,
C.COMMENT1,
C.TYPE1
FROM Table3 AS P
JOIN Table4 AS A ON P.ID1=A.ID1 AND P.ID2=A.ID2 AND P.ID3=A.ID3 AND P.spc=A.spc
INNER JOIN Table5 AS C ON P.ID1=C.ID1 AND P.ID2=A.ID2 AND P.ID3=C.ID3 AND P.spc=C.spc
WHERE C.TYPE1='FEC'
AND P.spc='BOCC'
AND ((P.Field5 ='Com' and P.Field6 IN ('Misc', 'Bl'))
OR (P.Field5 ='Residential' and P.Field6 IN ('SF', 'MH')))
AND COMMENT1 IS NOT NULL
AND P.Field3 <> 'Void'
AND P.Field1 NOT IN (SELECT PROJECTID FROM Test_Elevations)
)
AS TableToBePivoted
PIVOT
(
MAX(COMMENT1)
FOR desc1 IN ([Community Number], [Panel], [Datum], [Base Flood Elevation], [Lowest Adjacent Grade], [Lowest Machinery Elevation], [Date of Firm], [Firm Zone], [License Number], [Floor Elevation])
) AS PivotedTable
-- check for dupes on ProjectID
SELECT ProjectID, COUNT(*)
FROM #PivotedSourceTable
GROUP BY ProjectID
HAVING COUNT(*) > 1
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 8, 2013 at 8:20 am
No Duplicates
(0 row(s) affected)
Table '#PivotedSourceTable
The ProjectID field does not seem to be the issue. The PK_Test_ELE_761abed01e1455b5 field that shows up under the key folder (see original post screen shot) when I create the new table is the field that is generating the error message. I don't know what this field is or how it got created.
April 8, 2013 at 8:33 am
SDG1 (4/8/2013)
No Duplicates(0 row(s) affected)
Table '#PivotedSourceTable
The ProjectID field does not seem to be the issue. The PK_Test_ELE_761abed01e1455b5 field that shows up under the key folder (see original post screen shot) when I create the new table is the field that is generating the error message. I don't know what this field is or how it got created.
The ProjectID column is designated a primary key and so will not accept duplicates. Every new value added to ProjectID is checked against existing values to ensure it's not already there. If there's no index on ProjectID then a table scan is performed. SQL Server will by default create a (clustered) index to support this checking process. That's what the object PK_Test_ELE_761abed01e1455b5 is - the index to support/enforce the PK unique constraint.
I agree with earlier posts - it's most likely that you occasionally have dupes in your table source. Not necessarily your source tables. It may be the query which is generating the dupes.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 9, 2013 at 5:57 am
Ok, so I recreated the table defining the Project ID as UNIQUE, NOT NULL rather than as a primary key and everything works great now. There are no nulls in my projectID field. Anyone know why it would works as unique but not as a primary key?
April 9, 2013 at 6:12 am
SDG1 (4/9/2013)
Ok, so I recreated the table defining the Project ID as UNIQUE, NOT NULL rather than as a primary key and everything works great now. There are no nulls in my projectID field. Anyone know why it would works as unique NOT NULL but not as a primary key?
They are, for all intents and purposes, the same.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply