Temp table with converted values

Question

I want script to convert my table

CREATE TABLE #TempTable (
Code nvarchar(5) primary key,
name nvarchar(100) )

Insert into #TempTable (Code ,name) 
Select st.Code , st.name From StaticTable st

But there is something I must change.

Code must be autoincremented from 100

Where length of code is more than 3, then I want insert there integer, when not I want to copy this Code

So from

    Code   Name
    ABCD   Namezxc
    EFGH   Nameasd
    IJK    Nameqwe

I want get temptable with records:

Code Name
100    Namezxc
101    Nameasd
IJK  Nameqwe

Best regards

Answer 1

Try use this:

SELECT Case When Len(st.Code) <4 THEN IDENTITY(int, 100,1) 
           Else st.Code End As Code,
       st.name
INTO #TempTable 
FROM StaticTable  st

see more here and here

Answer 2

First insert all values, then go back and update the ones with LEN() > 3. Just use a shady(because its a nvarchar and you have to cast) increment variable.

CREATE TABLE #TempTable (
    Code nvarchar(5) primary key,
    name nvarchar(100) 
)

Insert into #TempTable (Code ,name) 
Select st.Code , st.name From (
   select 'ABCD' AS Code, 'Namezxc' as name union all
   select 'EFGH' AS Code, 'Nameasd' as name union all
   select 'IJK' AS Code, 'Nameqwe' as name 
) st

declare @vintCounter as nvarchar(5)
set @vintCounter = N'99'

update #TempTable
     SET @vintCounter = Code = cast(@vintCounter as int) + 1
where len(Code) > 3

select * from #TempTable