0

Let's say I have this table with the identity column pkid, how can I generate the cust_num as VIP000000 + <pkid> when inserting a record like this?

pkid cust_name cust_num
1 Tom VIP0000001
2 May VIP0000002
10 John VIP0000010 
CREATE TABLE [dbo].[tbl01]
(
    [pkid] [numeric](9, 0) IDENTITY(1,1) NOT NULL,
    [cust_name] [varchar](50) NOT NULL,
    [cust_num] [varchar](20) NOT NULL
) ON [PRIMARY]
Dale K
  • 25,246
  • 15
  • 42
  • 71
user1302236
  • 31
  • 1
  • 4

1 Answers1

5

The easiest solution based on an IDENTITY column is to use a computed column - like this:

ALTER TABLE dbo.tbl01
    ADD cust_num AS 'VIP' + RIGHT('000000' + CAST(pkid AS VARCHAR(6)), 6) PERSISTED;

Now, every time you insert a row into tbl01 without specifying values for pkid or cust_num:

INSERT INTO dbo.tbl01 (Col1, Col2, ..., ColN)
VALUES (Val1, Val2, ....., ValN)

then SQL Server will automatically increase your pkid value, and cust_num will contain values like VIP000001, VIP000002,...... and so on - automatically, safely, reliably, no duplicates.

UPDATE

Since you can't change your table definition, you will most likely need to use an AFTER INSERT trigger - something like this:

CREATE TRIGGER trgTbl01AddCustNum
AFTER INSERT
ON dbo.tbl01
AS
BEGIN
    UPDATE tbl01
    SET cust_num = 'VIP' + RIGHT('000000' + CAST(pkid AS VARCHAR(6)), 6)
    FROM tbl01
    INNER JOIN Inserted i ON i.pkid = tbl01.pkid;
END;
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • but i can't alter the table, is it possible handle by the insert statement? – user1302236 Aug 31 '22 at 03:54
  • 1
    @user1302236 you didn't put that requirement in your question, so I hope you didn't down-vote Mark because of that? And no you can't use that in your insert, because you don't know your id until after the insert completes. So you either need a trigger - but if you can't modify the table I suspect you can't create a trigger or you need to follow your insert with an update statement. – Dale K Aug 31 '22 at 03:56
  • 1
    @jmvcollaborator: **NOT** if you add the `PERSISTENT` keyword to the computed column specification ! – marc_s Aug 31 '22 at 03:57
  • you are correct, take that back, upvote :) – jmvcollaborator Aug 31 '22 at 03:59
  • 1
    @jmvcollaborator: plus I've added a solution *without* changing the table structure now .... – marc_s Aug 31 '22 at 04:01