1

After no row deletion, but after getting the 60 existing rows by local subscription to replication from another SQL Server instance, I'm inserting new rows with:

INSERT INTO [Business].[dbo].[ImagesTable] (Filename, Title, Price, PriceString, Category, CategoryRank) 
VALUES ('vegan1.jpg', 'vegan1', 380000, '380,000', 'delices vegan', 0)

But this will fail since the ID will start over somewhere (9 in this case), instead of starting at the last ID used +1, ie., 61 in my case.

System.Data.SqlClient.SqlException: 'Violation of PRIMARY KEY constraint 'PK_ImagesTable'. Cannot insert duplicate key in object 'dbo.ImagesTable'. The duplicate key value is (9).

My table is set to have autoincrement, therefore I should not have to use explicit IDENT_CURRENT('ImagesTable'). Why is this happening ?

Furthermore and despite this, when I try to insert explicitly ID I have:

Cannot insert explicit value for identity column in table 'ImagesTable' when IDENTITY_INSERT is set to OFF.

What is the recommended way to add new rows? How can I insert new rows with the ID being automatically set?

If the presence of DB replication is not propagating the Identity seed, how should I deal with this so I don't have to manage duplicate key errors (I'm certain that I'm not inserting duplicates)?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Soleil
  • 6,404
  • 5
  • 41
  • 61
  • Did you had a delete on your records before inserting? – Himanshu Oct 12 '19 at 22:34
  • For the explicit though you can SET IDENTITY_INSERT ON and then insert – Himanshu Oct 12 '19 at 22:36
  • And if theres no deletion performed then how the id will become 61 if autoincrement id has current value as 9. I din get it whats 9 in this case will fail means – Himanshu Oct 12 '19 at 22:39
  • I guess you are inserting duplicates – Himanshu Oct 12 '19 at 22:41
  • Umm.. either way that too wouldve worked as autoincrement would have created new id for those records too – Himanshu Oct 12 '19 at 22:43
  • Possible duplicate of [Reset AutoIncrement in SQL Server after Delete](https://stackoverflow.com/questions/510121/reset-autoincrement-in-sql-server-after-delete) – Himanshu Oct 12 '19 at 22:45
  • Can you post theTables complete DDL? Also any triggers too. – Mark Kram Oct 12 '19 at 23:32
  • @MarkKram What is DDL ? – Soleil Oct 13 '19 at 00:01
  • 1
    @Soleil, `DDL` is the data definition language subset of the SQL language (e.g. `CREATE TABLE` statements). – Dan Guzman Oct 13 '19 at 00:54
  • "Merge replication and transactional replication with updating subscriptions use uniqueidentifier columns to guarantee that rows are uniquely identified across multiple copies of the table." [uniqueidentifier (Transact-SQL)](https://learn.microsoft.com/en-us/sql/t-sql/data-types/uniqueidentifier-transact-sql) – AlwaysLearning Oct 13 '19 at 01:04

1 Answers1

1

You need to segment your IDENTITY column ranges in bidirectional replication.

https://learn.microsoft.com/en-us/sql/relational-databases/replication/publish/replicate-identity-columns?view=sql-server-ver15

Alex
  • 4,885
  • 3
  • 19
  • 39