CODEWITHSUNDEEP
sqlsql-serverinsert
Thildemar
Thildemar

Reputation: 99

"insert" new row at lowest identity in SQL Server

I have a somewhat ugly problem of dealing with an existing database. This database has a Transaction_Items table that contains the line items for each transaction (simplified):

Transaction_Item_ID(Identity - Auto)  Transaction_ID   Item_Name
-----------------------------------------------------------------
1                                      12345            Service1
2                                      12345            Service2
3                                      12345            Service3
4                                      12346            Service1

These transactions show up on an invoice and the application always orders them on that invoice based on the Transaction_Item_ID.

I need to add a service item to particular transactions that will always appear first, let's call its item name MEMO. Otherwise the items need to remain in the same order. Therefore this MEMO item will always need to have the lowest Transaction_Item_ID out of those that match the transaction. Because Transaction_Item_ID is an auto identity column I cannot just change that to a lower value. I think I basically need to update the first row's Item_name to MEMO while copying the existing item name to the next lowest row and so on until the previous last row value is inserted into a new row.

The question is how to do this or if there is a better way of achieving the same result.

Basically ending like this:

Transaction_Item_ID(Identity - Auto)  Transaction_ID   Item_Name
-----------------------------------------------------------------
1                                      12345            MEMO
2                                      12345            Service1
3                                      12345            Service2
4                                      12346            Service1
5                                      12345            Service3

EDIT To clarify: I do not have access to change the application that is otherwise using this table. I cannot add a new column to the table.

I know that the identity value cannot/should not be changed as that would cause a mess. That is why I assume the only answer would be to "move" the Item name value "down" the rows.

Yes, this is ugly and far from ideal. Unfortunately dealing with legacy applications and making the best of it is a reality in many businesses. At least I actually have read/write access to the database.

Upvotes: 0

Views: 130

Answers (1)

TJB
TJB

Reputation: 877

I don't like your idea of updating each row.

Your simplest solution in this scenario is to rename this table and use it as a staging table, then perform an insert into a new table (which has the name your original table used to have) using the desired ordering.

Or, change the ordering in the application.

Upvotes: 1

Related Questions