I'm using the Table Value constructor to insert a bunch of rows at a time.

However if i'm using sql replication, I run into a range check constraint on the publisher on my id column managed automatically.

The reason is the fact that the id range doesn't seem to be increased during an insert of several values, meaning that the max id is reached before the actual range expansion could occur (or the id threshold).

It looks like this problem for which the solution is either running the merge agent or run the sp_adjustpublisheridentityrange stored procedure.

I'm litteraly doing something like :

INSERT INTO dbo.MyProducts (Name, ListPrice)
VALUES ('Helmet', 25.50),
       ('Wheel', 30.00),
       ((SELECT Name FROM Production.Product WHERE ProductID = 720),
        (SELECT ListPrice FROM Production.Product WHERE ProductID = 720));
GO

What are my options (if I don't want or can't adopt any of the proposed solution) ? Expand the range ? Decrease the threshold ?

Can I programmatically modify my request to circumvent this problem ?

thanks.