sql server - Avoiding a two step insert in SQL -

let's have table defined follows:

create table sometable ( p_id int primary key identity, compoundkey varchar(255) not null, ) 

compoundkey string primary key p_id concatenated end, foo00000001 comes "foo" + 00000001. @ moment, entries insertions table happen in 2 steps.

1. insert dummy record place holder string compoundkey.
2. update compoundkey column generated compound key.

i'm looking way avoid 2nd update entirely , 1 insert statement. possible? i'm using ms sql server 2005.

p.s. agree not sensible schema in world, , schema refactored (and normalized) i'm unable make changes schema now.

your use computed column; change schema read:

create table sometable ( p_id int primary key identity, compoundkeyprefix varchar(255) not null, compoundkey compoundkeyprefix + cast(p_id varchar(10)) ) 

this way, sql server automagically give compound key in new column, , automatically maintain you. may want persist keyword computed columns cause sql server materialise value in data files rather having compute on fly. can add index against column should wish.