Hi,

We are trying to copy data between databases. However we are having problems with tables with columns "generated by default as identity". Once data is transferred to another DB, the identity column doesn't detect that data already exists, thus causing a duplicate key error on the next insert. It would have been ok to just regenerate these values but they are linked to other tables as foreign keys.

How could I work around this? Is there a way to set the "next available value" for identity columns?

One way I thought of is to determine the MAX value of that column to be inserted, then insert that number of "dummy" records into the new table. Delete the "dummy" records, then insert/import the real data. A duplicate key error should no longer appear. However this seems like an extreme solution.

Thanks in advance

Use:
ALTER TABLE mytable ALTER COLUMN identityCol restart with x

where x is max+1 of current identity values.

HTH

Andy

Thanks Andy, that's exactly what I needed!