populate auto-increment INT 5 digit value in case of null in Sql server

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

, +

I have a secnario where I need to auto generate a value of a field if in case if it is null.

Ex :TABLE 1. employeeDetails

SQL

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

TABLE 2. employeeDepartment

SQL

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

QUERY

SQL

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

RESULT :

SQL

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:

SQL

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 field datatype.

Posted 6-Oct-15 2:19am

praveen_07

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Andy Lanng · Answer 1 · 2015-10-06T03:04:00

Common Table Expressions (CTE) will always save the day

SQL

with aCTE as (
	SELECT 
		empdt.empId, 
		empdprt.deptName, 
		empdt.empExtension,
		row_number() over(partition by case when empdt.empExtension is null then 1 else 0 end order by empdt.empId) as num -- generate sequential numbers
	FROM 
		employeeDetails empdt
		LEFT JOIN employeeDepartment empdprt ON empdt.empId = empdprt.empId
		)
select 
	a.empId,
	a.deptName,
	isnull(a.empExtension,num) --replaces null empExtension with the num
from aCTE a