Problem Copying Records in Table Having Identity Column 1

[JohnBates]

hi all,

I've copied tables that have an identiy col before, but this one has me stumped.

Both the from and To tables have identical layout and both of course have the Identity col in the same place.

Get error Violation of PRIMARY KEY constraint 'PK_FundTransactions'. Cannot insert duplicate key in object 'Fund Transactions'.

The PK is the Identity column.

I have this:

SET IDENTITY_INSERT dbo.[Fund Transactions] ON
INSERT INTO TAHOE.gm.dbo.[Fund Transactions]
([Dlr Num],
[Promo],
[Trans Type],
[Period],
[Trans Amt],
[Comment],
[Trans Date],
[Product Code],
[Product Group],
[Sales],
[Units],
[Last Modified],
[Operator],
[Fund Misc 1],
[Fund Misc 2],
[Fund Misc 3],
[Fund Misc 4],
[Fund Misc 5],
[ACB Comment],
ID,
[FileNumberIn],
[FileLineNumber])

SELECT
[Dlr Num],
[Promo],
[Trans Type],
[Period],
[Trans Amt],
[Comment],
[Trans Date],
[Product Code],
[Product Group],
[Sales],
[Units],
[Last Modified],
[Operator],
[Fund Misc 1],
[Fund Misc 2],
[Fund Misc 3],
[Fund Misc 4],
[Fund Misc 5],
[ACB Comment],
ID,
[FileNumberIn],
[FileLineNumber]
FROM GM.gm.dbo.[Fund Transactions]
WHERE ([Dlr Num] = '00000008524' OR
[Dlr Num] = '00000008506' OR

[Dlr Num] = '00000008623' OR

[Dlr Num] = '00000008505' OR

[Dlr Num] = '00000008533' OR

[Dlr Num] = '00000008528') AND (Operator <> 'mis load')

 

[vongrunt]

This probably has nothing related with identity column. Query simply attempts to insert row with existing primary key value => PK violation.

Check this with inner join query:

select A.*
from TAHOE.gm.dbo.[Fund Transactions] A
inner join GM.gm.dbo.[Fund Transactions] B on <join primary keys here>
where B.[Dlr Num] IN ('00000008524', '00000008506', '00000008623', '00000008505', '00000008533', '00000008528') 
	AND (B.Operator <> 'mis load')

If that returns 0 rows... then dupes are entirely in source table (B). Or maybe some underlying triggers cause weirdos.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[JohnBates]

Thanks vongrunt.

The table has 2 more indexes, but they are not Unique.

The PK (Identity) col is the only PK/index with Unique attribute.

But why doesnt SQL Server just assign the identity value in the 'To' table ?

This is wierd. 1 more reason why I don't like Id columns.

I'm stuck.

John

 

[vongrunt]

I assume current server is TAHOE, database GM? Otherwise server would complain about identity inserts.

> But why doesnt SQL Server just assign the identity value in the 'To' table ?

It tried (that's why identity insert is used in a first place, right?) - but PK already exists.

Btw. how many rows returns query I posted?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[JohnBates]

vongrunt,
Yes the current server is TAHOE.
12 rows were returned by:

select A.*
from TAHOE.gm.dbo.[Fund Transactions] A
inner join GM.gm.dbo.[Fund Transactions] B on B.ID = A.ID
where B.[Dlr Num] IN ('00000008524', '00000008506', '00000008623', '00000008505', '00000008533', '00000008528')
AND (B.Operator <> 'mis load')

 

[vongrunt]

Are these rows identical in both tables?

If true, you don't need to do anything

If false, then things look like some crude version of replication conflict - same PK, different data.

Or maybe data from A can/must be UPDATEd with data from B?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[JohnBates]

I do run replication. But this table is not one of the articles.

No, the data is not identical on both servers. I thought the Identity attribute might be 'off' on TAHOE (the To Table) but it is still defined as Identity yes.

Even though we haven't solved this yet, I want to Thank you for the time you have spent on this. It is weird.

Table has 19 million rows - otherwise I would just drop the PK column in the Tahoe table and re-add it later.

John

 

[JohnBates]

I finally got the copy to work.

I used
SET IDENTITY_INSERT gm.dbo.[Fund Transactions] OFF,
then named every column - except the Identity column - in both the Insert and Select stmts.

I don't remember ever having to do it that way before.

Thanks to vongrunt for hanging in there with me.

John