CODEWITHSUNDEEP
sqlsql-server
Vikas J
Vikas J

Reputation: 887

SQL 2014 - How to add incremental value for each row of non identity column

I have 2 tables (Dim & User). In Dim table, there is the EmpId column which has incremental values (1,2,..) but is not an identity column and the User table is in join with Dim table based on SalesKey.

There are 3 rows that are missing in Dim which exists in the User table. I want to insert the missing rows in Dim table, but the catch is while inserting the EmpId column needs to get incremental values for new rows.

So far queries I tried is as below, which gives me results in split, but I am not able to merge results in a single query, maybe nested subquery will help but not sure how?

Create table DimEmp
(
   EmpId bigint not null,
   SalesKey varchar(10),
   EmpName varchar(100)
   CONSTRAINT PK_DimEmp_EmpId PRIMARY KEY (EmpId)
)    
GO    
INSERT INTO DimEmp (EmpId,SalesKey,EmpName)
VALUES (1,'001A','John'), (2,'002B','Stephen')    
GO    
Create table [User]
(
   UserId varchar(10),
   EmpName varchar(100)
   CONSTRAINT PK_User_UserId PRIMARY KEY (UserId)
)    
GO    
INSERT INTO [User] (UserId,EmpName)
VALUES ('001A','John'), ('002B','Stephen'), 
('003C','Bruce'), ('004D','Clark'),('005E','Mitchel')    
GO
SELECT u.UserId,u.EmpName
FROM [User] u
LEFT JOIN DimEmp d
ON d.SalesKey=u.UserId
WHERE d.SalesKey IS NULL   -- prints missing 3 records of Dim    
GO        
SELECT 1 + EmpId + 1 AS NewincrEmpId,
(  SELECT MAX(EmpId) FROM DimEmp
 ) AS MaxEmpid
FROM DimEmp        -- inner query gives max empid and outer query increments value for each row    
GO

Expected Output in Dim table after inserting 3 new records using INSERT INTO SELECT (subquery) statement

enter image description here

Upvotes: 3

Views: 848

Answers (3)

Shushil Bohara
Shushil Bohara

Reputation: 5656

TRY: This is also better way of using OUTER APPLY to get max EmpId and ROW_NUMBER to get the desired output as below

SELECT ISNULL(tt.NewincrEmpId, 0)+ROW_NUMBER() OVER(ORDER BY u.UserId ASC) AS NewincrEmpId,
    u.UserId,
    u.EmpName
FROM User u
LEFT JOIN DimEmp d ON d.SalesKey=u.UserId
OUTER APPLY(SELECT MAX(de.EmpId) AS NewincrEmpId
            FROM DimEmp de) tt

OUTPUT: 

NewincrEmpId    UserId  EmpName
3               003C    Bruce
4               004D    Clark
5               005E    Mitchel
WHERE d.SalesKey IS NULL

Upvotes: 0

sticky bit
sticky bit

Reputation: 37472

Sounds like you got a serious design issue there.

For a quick fix you can use row_number() and add it to the maximum ID.

INSERT INTO [dimemp]
            ([empid],
             [saleskey],
             [empname])
            SELECT (SELECT coalesce(max(de1.[empid]), 0)
                           FROM [dimemp] de1) + row_number() OVER (ORDER BY u1.[userid]),
                   u1.[userid],
                   u1.[empname]
                   FROM [user] u1
                   WHERE NOT EXISTS (SELECT *
                                            FROM [dimemp] de2
                                            WHERE de2.[saleskey] = u1.[userid]);

db<>fiddle

Upvotes: 1

Aducci
Aducci

Reputation: 26674

  1. You will need to wrap the whole thing into a transaction.
  2. Grab the max(EmpId) using serializable table lock, to make sure no other process adds/modifies EmpId
  3. use row_number to get the new unique ids

Query:

begin tran

declare @maxid bigint
set @maxid =
(   
    select max(EmpId) from DimEmp with(serializable)
)

insert into DimEmp
(
    EmpId,
    SalesKey,
    EmpName
)
select
    isnull(@maxid, 0) +
    row_number() over (order by u.UserId),
    u.UserId,
    u.EmpName
from
    [User] as u
left join
    DimEmp as d on
    d.SalesKey = u.UserId
where
    d.SalesKey is null

commit tran

Upvotes: 1

Related Questions