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: 
You should be able to do that with a CTE to grab [ROW\_NUMBER](https://msdn.microsoft.com/en-us/library/ms186734.aspx), and then [COALESCE](https://msdn.microsoft.com/en-us/library/ms190349.aspx) to only use that number where the value is NULL:

```
WITH cte AS(
  SELECT empId, empExtension, ROW_NUMBER() OVER(ORDER BY empExtension, empId) rn
  FROM employeeDetails
)
SELECT cte.empId, deptName, COALESCE(empExtension, rn + 12344) empExtension
FROM cte LEFT JOIN employeeDepartment
  ON cte.empID = employeeDepartment.empID
ORDER BY cte.empId

```

Here's an [SQLFiddle](http://sqlfiddle.com/#!3/142d1/5).
```
 declare @rand int = (rand()* 12345);
SELECT empdt.empId, empdprt.deptName, 
        isnull(empdt.empExtension,row_number() over(order by empdt.empId)+@rand)
   FROM employeeDetails empdt
LEFT JOIN employeeDepartment empdprt
   ON empdt.empId = empdprt.empId

```

Incase the empExtension, get the row\_number + a random number.