Avoiding a two step insert in SQL

Let's say I have a table defined as follows:

CREATE TABLE SomeTable
(
P_Id int PRIMARY KEY IDENTITY,
CompoundKey varchar(255) NOT NULL,
)

CompoundKey is a string with the primary key P_Id concatenated to the end, like Foo00000001 which comes from "Foo" + 00000001. At the moment, entries insertions into this table happen in 2 steps.

1. Insert a dummy record with a place holder string for CompoundKey.
2. Update the CompoundKey with the column with the generated compound key.

I'm looking for a way to avoid the 2nd update entirely and do it all with one insert statement. Is this possible? I'm using MS SQL Server 2005.

p.s. I agree that this is not the m开发者_运维知识库ost sensible schema in the world, and this schema will be refactored (and properly normalized) but I'm unable to make changes to the schema for now.

---

Your could use a computed column; change the schema to read:

CREATE TABLE SomeTable
(
P_Id int PRIMARY KEY IDENTITY,
CompoundKeyPrefix varchar(255) NOT NULL,
CompoundKey AS CompoundKeyPrefix + CAST(P_Id AS VARCHAR(10))
)

This way, SQL Server will automagically give you your compound key in a new column, and will automatically maintain it for you. You may also want to look into the PERSIST keyword for computed columns which will cause SQL Server to materialise the value in the data files rather than having to compute it on the fly. You can also add an index against the column should you so wish.

---

A trigger would easily accomplish this

---

This is simply not possible.
The "next ID" doesn't exist and thus cannot be read to fulfill the UPDATE until the row is inserted.

Now, if you were sourcing your autonumbers from somwhere else you could, but I don't think that's a good answer to your question.

Even if you want to use triggers, an UPDATE is still executed even if you don't manually execute it. You can obscure the population of the CompoundKey, but at the end of the day it's still going to be an UPDATE

I think your safest bet is just to make sure the UPDATE is in the same transaction as the INSERT or use a trigger. But, for the academic argument of it, an UPDATE still occurs.

---

Two things:

1) if you end up using two inserts, you must use transaction! Otherwise other processes may see the database in inconsistent state (i.e. seeing record without CompoundKey).

2) I would refrain from trying to paste the Id to the end of CompoundKey in transaction, trigger etc. It is much cleaner to do it at the output if you need it, e.g. in queries (select concat(CompoundKey, Id) as CompoundKeyId ...). If you need it as a foreign key in other tables, just use the pair (CompoundKey, Id).