How to insert data into table while incrementing certain column

Question

I would like to know is it possible to perform as per subject.
Example as per attachment.

From that table, I would like to insert the value as default but the 'Card_ID' column I would like the value to keep increment until a specific value.

Answer 1

Hi @Zetsubo6996 ,

Please try:

ALTER TABLE tablename ADD id INT IDENTITY(1,1)  
  
UPDATE t  
SET Card_ID=CONCAT('BSH-CF',id)  
FROM tablename t  

The above code can make the Card_ID column increment to the last row in your table.

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

If the Card_ID format is 'BSH-CFXXX' (XXX are the digits), try this:

INSERT INTO YourTable
SELECT TOP 1 
    Created_Date,
    Created_User,
    Updated_Date,
    Updated_User,
    Update_Frequency,
    LEFT(Card_ID, 6) + RIGHT('00' + CAST(CAST(RIGHT(Card_ID, 3) AS int) + 1 AS varchar(3)), 3),
    Card_Color,
    Category_ID,
    Person_ID,
    Active
FROM YourTable
ORDER BY Card_ID;