New answers tagged auto-increment
Just found the solution after banging my head into the wall: SELECT t.*, @row := IF(@lastState = t.State, @row + 1, 0) as counter, @lastFunkt := t.State FROM table t, (SELECT @row := 0, @lastState := '') SQLVars
Using UPDATE, DELETE or INSERT to move data can take quite a lot of time and use resources (IO) on both data and log files/disks. It is possible to avoid filling the transaction log with potentially lots of records while working on a big table: Using partition switching, only the metadata is changed. There is no data movement involved and this is therefore ...
The way I understand your question is that you have an existing table with a column that has up until now been populated with manual values, and now you want to (1) make this column an IDENTITY column, and (2) make sure that the IDENTITY starts from the most recent value in the existing rows. First off, some test data to play with: CREATE TABLE ...
If you want to start with a new identity value you need to reseed your identity. Have a look at the documentation for CHECKIDENT DBCC CHECKIDENT (yourtable, reseed, starting point)
Top 50 recent answers are included