I have a table called tblOrders with an integer column called OrderId which is designated as Generate As Identity Always. I'm building an archive process where selected rows from the tblOrders table get inserted into the tblOrdersArchive table, and then the original rows are deleted from the tblOrders table. The idea is to archive older orders by moving them out of the main table.

I have a scenario where some orders were archived by mistake. They now they need to be re-inserted back into the tblOrders table retaining the original OrderId value. Since the OrderId column is designated as Generate As Identity Always,  how do I insert these rows back into the tblOrders table and retain the original OrderId value?

I'm not looking for a "one-time" fix. I'm looking to build a process that can be used over again, anytime rows are mistakenly archived.

In my mind it seems like I would perform the following:

1. Temporarily turn off Identity insert for tblOrders
2. Insert rows into tblOrders including the OrderId value
3. Turn Identity insert back on for tblOrders

How do you do this within ElevateDB?

Thanks in advance.

Michael Riley
GySgt USMC (Ret)
www.zilchworks.com

Michael


>1. Temporarily turn off Identity insert for tblOrders
>2. Insert rows into tblOrders including the OrderId value
>3. Turn Identity insert back on for tblOrders
>
>How do you do this within ElevateDB?

What you have to do is alter the column type. This would be done using SQL, and you can find the exact statements needed by doing it in EDBManager and then looking at Explorer | SQL History

However, if the orders table is quite large then altering the column definitions will take a bit of time, and it needs to be done on an exclusive basis. What I'd recommend is altering the column to


INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 0, INCREMENT BY 1) NOT NULL,

Not quite as secure as

INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1) NOT NULL

from a viewpoint of preventing someone typing in their own order number but a lot more convenient for things sus as you want to do.

Roy Lambert

Roy,

<<
What you have to do is alter the column type. This would be done using SQL, and you can find the exact statements needed by doing it in EDBManager and then looking at Explorer | SQL History

However, if the orders table is quite large then altering the column definitions will take a bit of time, and it needs to be done on an exclusive basis. What I'd recommend is altering the column to


INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 0, INCREMENT BY 1) NOT NULL,

Not quite as secure as

INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1) NOT NULL

from a viewpoint of preventing someone typing in their own order number but a lot more convenient for things sus as you want to do.
>>

Thank you. That is exactly what I was looking for.

Michael Riley
GySgt USMC (Ret)
www.zilchworks.com