Sorry if that's not overly clear.

In SQL Server, can I have a field which I can place a string into, and then SQL Server appends an auto-number onto the end?

So for example, say I have column A.

  • If I set row 1 in column A to "Foo", the final value will be "Foo1"
  • Then if I set row 2 in column A to "Bar", the final value is "Bar2"

Is this possible?


Yes use a Calculated Value column. For example:

ALTER TABLE Table1 ADD ColumnB AS ColumnA + CAST(ID AS varchar(8)) PERSISTED

Assuming ID is where you're getting row 1 or row 2 value.


Another a way would be based on a view. If you can't modify the source table - often the case for 3rd party products - a view can save your day. Like so,

create table autoappend(id int identity(1,1) , data nvarchar(32))
insert into autoappend(data) values ('foo')
insert into autoappend(data) values ('bar')
insert into autoappend(data) values ('zof')

create view vAppend as
    select id
        , data
        , appended = data + convert(nvarchar(8), id)  -- concat() is SS2012+ only
    from autoappend

select * from vAppend


id  data    appended
1   foo foo1
2   bar bar2
3   zof zof3
  • appended is a column alias. I suggest you remove the single quotes. – ypercubeᵀᴹ Sep 16 '13 at 11:10
  • @ypercube, Thanks. Didn't remember that. I Guess old bad habits are hard to kick – vonPryz Sep 16 '13 at 11:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.