SQL INSERT: Increment an int value before insert

Question

I have a variable,

DECLARE @CN int = (SELECT [CONTROL_NUMBER] FROM myTable)

I need to do an insert statement where the value of this variable is incremented during run time.

So,

INSERT INTO myTable2 (ControlNumber)
SELECT CNC+(@CN increment the value here) FROM aTable

So the values inserted will be like,

aa1234
ab1235
cd1236...

etc.

Definitely @CN is an int, so I will account for changing it to string before inserting the value.

Appreciate the help.

Thanks!

Ideally one should just leave the handling of the sequence values up to the database. Why this approach? — Joseph Victor Zammit, May 08 '12 at 20:52
I already have an identity column. The value needs to be inserted during run time based on the number of rows already inserted into the database. — OBL, May 08 '12 at 21:04
I have an identity column, this is some arbitrary value that I get from another table and need to increment it for every new row inserted. — OBL, May 08 '12 at 21:45
I got it resolved through this: http://stackoverflow.com/questions/2820668/insert-into-statement-that-copies-rows-and-auto-increments-non-identity-key-id-c — OBL, May 08 '12 at 21:54

score 1 · Answer 1 · answered May 09 '12 at 03:58

1

SET @CN = @CN + 1
INSERT INTO myTable2 (ControlNumber) 
SELECT CNC + Cast(@CN as varchar) FROM aTable

answered May 09 '12 at 03:58

Warren Rox

Event though OP should probably being using an Identity column and a separate VARCHAR/CHAR column, this does technically answer his question. +1 @OBL, please rethink your design here. – Tom Halladay May 10 '12 at 03:42

1 Answers1