Question: I have a scenario where I need to auto generate the value of a column if it is null.

Ex: `employeeDetails`:

```
empName empId  empExtension
  A      101   null
  B      102   987
  C      103   986
  D      104   null
  E      105   null

```

`employeeDepartment`:

```
deptName  empId   
   HR      101
   ADMIN   102
   IT      103
   IT      104
   IT      105

```

Query

```
SELECT 
    empdt.empId, empdprt.deptName, empdt.empExtension 
FROM 
    employeeDetails empdt
LEFT JOIN 
    employeeDepartment empdprt ON empdt.empId = empdprt.empId

```

Output: 

```
empId deptName empExtension
 101    HR          null
 102    ADMIN       987
 103    IT          986
 104    IT          null
 105    IT          null

```

Now my question is I want to insert some dummy value which replaces null and auto-increments starting from a 5 digit INT number

Expected output:

```
empId     deptName    empExtension
 101    HR          12345
 102    ADMIN       987
 103    IT          986
 104    IT          12346
 105    IT          12347

```

Constraints : I cannot change existing tables structure or any column's datatypes.

Answer: 
If you just want to create a unique random 5 digit number for those `empExtension` column values are null, then 

**Query**

```
;with cte as
(
    select rn = row_number() over
    (
        order by empId
    ),*
    from employeeDetails
)
select t1.empId,t2.deptName,
case when t1.empExtension is null 
then t1.rn + (convert(numeric(5,0),rand() * 20000) + 10000) 
else t1.empExtension end as empExtension
from cte t1
left join employeeDepartment t2
on t1.empId = t2.empId;

```

[**SQL Fiddle**](http://www.sqlfiddle.com/#!3/29acb/1)
Just an idea, can you save result of that query into temp table 

```
SELECT empdt.empId, empdprt.deptName, empdt.empExtension
INTO #TempEmployee
FROM employeeDetails empdt
LEFT JOIN employeeDepartment empdprt
ON empdt.empId = empdprt.empId

```

And after that just do the update of #TempEmployee?