[SQL] How to set a autoincrement value in a If else case in SQL query

I am trying to select from a table and insert into another table in Microsoft SQL server where I need to check for a numeric value exist. If it is numeric then insert the actual value else insert a incremental integer value.

I tried below query but not sure how to make it incremental.

select custname
,CASE
WHEN ISNUMERIC(custid)=1 THEN custid ELSE 0000 END as [CustomerId]
from customerTable
Insert into tempTable name , id


Example

if the customer table has below data:



I need the temp table to be generated as below:



I need to ensure the non numeric values are auto incremented from 100.

How can I achieve it? Any help is appreciated.

Continue reading...