reset db table auto number for id
Question

Has anyone used advanced sql to reset the auto increment id number for a db table?  What I want to do is be able to delete all entries from a table then run this command so that the next save will be id # 1.

Hi Jason,


In the cloud, the default user used in the runtime DB connection does not have permissions for that operation.


Cheers,

Tiago.

Tiago Gafeira wrote:

Hi Jason,


In the cloud, the default user used in the runtime DB connection does not have permissions for that operation.


Cheers,

Tiago.

Tiago - we have an on prem installation and we can bump the permissions of the default user as this will ONLY be used on our test server.  


In that case you should have no problems to perform the operation. Just use regular OutSystems notation.

mvp_badge
MVP

I think the problem is that to reset the identity column (which is what an autonumber Attribute is), you need to specify the table name between quotes, like this:

DBCC CHECKIDENT ('tablename', RESEED, 1)

I haven't tried it myself, but I would probably get the physical table name from the Entity System Entity, and create a SQL query with an Expand Inline Input Parameter that contains the physical table name including the single quotes.

Kilian Hekhuis wrote:

I think the problem is that to reset the identity column (which is what an autonumber Attribute is), you need to specify the table name between quotes, like this:

DBCC CHECKIDENT ('tablename', RESEED, 1)

I haven't tried it myself, but I would probably get the physical table name from the Entity System Entity, and create a SQL query with an Expand Inline Input Parameter that contains the physical table name including the single quotes.

Should do it.


Jason Herrington wrote:

Has anyone used advanced sql to reset the auto increment id number for a db table?  What I want to do is be able to delete all entries from a table then run this command so that the next save will be id # 1.

Back to the original problem. A TRUNCATE should do exactly what you need. Both deleting the data and resetting the autonumber.


Tiago Gafeira wrote:

Jason Herrington wrote:

Has anyone used advanced sql to reset the auto increment id number for a db table?  What I want to do is be able to delete all entries from a table then run this command so that the next save will be id # 1.

Back to the original problem. A TRUNCATE should do exactly what you need. Both deleting the data and resetting the autonumber.


How would you do truncate through OutSystems - assuming the permissions are bumped up for the default user?


If the user has the right permissions, just use a regular TRUNCATE statement.

TRUNCATE TABLE {EntityName};

mvp_badge
MVP

Hi Jason,

What is the DBMS you are using, SQL Server?

Hi,  

Related question: https://www.outsystems.com/forums/discussion/74635/what-will-happen-after-the-id-attribute-get-full/ I have a question that is related with this post.  What will happen after the auto  biginteger number get full of usage (After a ton of insert and delete operations get its maximum value) will be reset or crash the application? 

mvp_badge
MVP

Hi Savvas,

You are opening a thread of 2 years ago. The better approach is to create a new topic, and if needed share a link to this old discussion.

Kind regards,

Daniel

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.