Trying to use sql to set the Restart values for an identity column using a value from another table. I tried:

ALTER TABLE filename ALTER COLUMN fieldname RESTART WITH ( Select MAX(masterFieldID)+ 1 from masterFileName);


It doesn't like the select, is there a way to do this in a script?

Here was my attempt:


Delete from CS_ContactX ;
Insert Into CS_ContactX (ContactID) Values ( Select MAX(ContactID)+ 1 from CS_Contact) ;
ALTER TABLE CS_ContactX ALTER COLUMN ContactID RESTART WITH ( Select MAX(ContactID)+ 1 from CS_Contact);
Select * From CS_ContactX;

The ALTER TABLE ... RESTART WITH statement can only have a numeric constant. No subselects. You will have to do this programatically. Either an Stored procedure or an application.

HTH

Andy