January 14, 2004 at 8:39 am
I have Tow Identical Tables In sperate servers
One of the columns is Identity (int)
Both tables are beeing updated
The Identity seed has a considrable gap between the 2 tables
If i turn the Identity_Insert On a table
and append rows from one table to onother
the Identity column looses the Place which it was on ,in the destination table
and the next insert to this table starts at the hieghst value
is there a way to override this behaviour
January 14, 2004 at 10:58 am
Exactly what part do you want to override, the seed value being the next available value?
Are these fields primary keys or is there otherwise a unique constraint? If not, then DBCC CHECKIDENT ('table_name', RESEED, new_reseed_value) can be used to reseed the table to a new value. I've enver used it to reseed it to a value that already exists though, so not sure how well it would work. I only use it to reseed merge replicated tables that are out of synch.
January 14, 2004 at 11:34 am
Sounds more like a data model issue then a syntax one. The tables are not actauly identical.
Is there a requirement for the identity values of each row to be the same in each table?
Do the tables need to be identical row by row, or mearly a 1-to-1 relationship based on that indentity col?
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors)
January 14, 2004 at 1:42 pm
I agree that the data model may need to be rethought, but I don't think he is trying to keep them identical. Frankly, I'm not sure what he's trying to do for sure.
January 14, 2004 at 2:20 pm
What you will want to do is use DBCC CHECKIDENT... to reseed your identity column to the value you want after doing the "merge" of the two tables. The problem is that what you are apparently trying to do is what merge replication does without the benefit of the replication engine and managed identities to help you out. Be aware that managed identities can be a real headache if they get out of sync!
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
January 14, 2004 at 2:41 pm
Isn't that what I just said?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply