3

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
  • 1,029
  • 3
  • 17
  • 38

2 Answers2

2

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.

mad
  • 1,029
  • 3
  • 17
  • 38
0

If I understand you want force identity. then try this 

SET IDENTITY_INSERT IdentityTable ON

INSERT IdentityTable(TheIdentity, TheValue)
VALUES (3, 'First Row')

SET IDENTITY_INSERT IdentityTable OFF

IdentityTable : your table TheIdentity: column identity TheValue: other column...

YannickIngenierie
  • 602
  • 1
  • 13
  • 37
  • Your solution does not work for me and the server gives me feedback: poQuery: Error 7200: AQE Error: State = 42000; NativeError = 2115; [iAnywhere Solutions][Advantage SQL Engine]Expected lexical element not found: Expecting keyword TRANSACTION or system variable identifier after SET. -- Location of error in the SQL statement is: 5 – mad Oct 26 '16 at 14:59
  • I do not know what should I do. Using keywords from your answer I found the solution here: http://devzone.advantagedatabase.com/forum/questions/41/copy-a-set-of-tables-with-autoinc-fields. Thanks a lot for your help. Should I up your answer? – mad Oct 26 '16 at 15:00
  • You can up my answer if you like it. But you need Answer your question and write the good code and click on "V" – YannickIngenierie Oct 27 '16 at 17:19
  • Invalid response. SET IDENTITY_INSERT is not SQL stadard. It's from MS SQL and for ADS SQL. On ADS only exists funktion AdsDisableAutoIncEnforcement (from ACE Library). This function doesn't resert AUTOINC values it only checkes for new maximum. https://devzone.advantagedatabase.com/dz/webhelp/advantage10/index.html?devguide_sql_scalar_functions.htm – Radek Secka Aug 09 '18 at 13:28
  • I mean: SET IDENTITY_INSERT is fir MS SQL and NOT for ADS SQL – Radek Secka Aug 09 '18 at 14:28