insert problems - help! - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

insert problems - help!

Author

Topic

Noodles
Starting Member

5 Posts

Posted - 2003-05-19 : 23:20:00

Hi,

I've been having some major issues with an insert query that i have.
It keeps on coming up with this error:
Server: Msg 2627, Level 14, State 1, Procedure AppendMovementImportTblToMovementTbl_Qry, Line 2
Violation of PRIMARY KEY constraint 'aaaaaMovement_Tbl_PK'. Cannot insert duplicate key in object 'Movement_Tbl'.
The statement has been terminated.

As far as i can see i'm not trying to insert records that already exist in the table, is there anyway to see exactly what record is causing the violation?

Thanks in advance

DavidD
Yak Posting Veteran

73 Posts

Posted - 2003-05-19 : 23:31:01

Post your proc AppendMovementImportTblToMovementTbl_Qry.
Are there any triggers on your table?

Noodles
Starting Member

5 Posts

Posted - 2003-05-19 : 23:38:01

Here's the proc:
CREATE PROCEDURE dbo.AppendMovementImportTblToMovementTbl_Qry
AS INSERT INTO dbo.Movement_Tbl
(MovementDate, RegistrationMark, MovementType)
SELECT DISTINCT cast(cast(datepart(yyyy,F1) AS varchar) + '-' + cast(datepart(mm,F1) AS varchar) + '-' + cast(datepart(dd,F1) AS varchar) + ' ' + cast(datepart(hh, F2) AS varchar) + ':' + cast(datepart(n, F2) AS varchar) + ':00' AS datetime) AS MovementDate, F3, F4
FROM dbo.MovementImport_Tbl
GO

There are no triggers on the table, but there's some foreign keys (not that it matters here, as it seems to have a problem with the primary key)

DavidD
Yak Posting Veteran

73 Posts

Posted - 2003-05-19 : 23:59:40

Is MovementDate the primary key?
If so it is quite possible for you to end up with a duplicate if F1 and F2 are the same (especially as seconds are'nt counted).
ie : This would result in a key violation
F1 F2
1/1/01 12:01:22
1/1/01 12:01:55

I would check the data coming from those fields.

Noodles
Starting Member

5 Posts

Posted - 2003-05-20 : 00:07:10

MovementDate and RegistrationMark are the primary key, and no seconds are counted, they're all 00

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources