mad mad - 20 days ago 6
SQL Question

Reset identity (auto increment field) in advantage db table

Please show me the way to reset auto increment field in a table of the Advantage Database Server 11.0.

In SQL Server, it works like this:

DBCC CHECKIDENT ('tableName', RESEED, 0);
GO


UPDATE: What I want is to write consecutive values (1,2,3,4....) into the autoincrement column.

When I use explicit SQL to insert the values

INSERT INTO TABLE1 (ID) VALUES (1);


I expect to see an "1" in the table. But I get next identity value instead.

SOLUTION is at the advantage support forum

mad mad
Answer

the solution is to change a type of the identity column to INTEGER and set it back to AUTOINC after updates made.

ALTER TABLE mytable ALTER COLUMN auto auto INTEGER;
INSERT INTO mytable SELECT * FROM myothertable;
ALTER TABLE mytable ALTER COLUMN auto auto AUTOINC;

The solution was found at the Advantage Database forum.