question

Zetsubo6996 avatar image
0 Votes"
Zetsubo6996 asked EchoLiu-msft commented

How to insert data into table while incrementing certain column

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.

sql-server-generalsql-server-transact-sql
image.png (9.1 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


I think that you can use a WHILE loop. Did you consider this simple approach?


0 Votes 0 ·

Could you have any update?Did the following methods solve your problem?If not, please provide more details.If it is resolved,please also remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered

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;
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.