Skip to content
Advertisement

Already inserted data replace into another UserId

In my Business unit table FK is the UserId

ex: UserId =001, BusinessUnit = Bangalore

ex: UserId =001, BusinessUnit = Hyd ….

Now, I need UserId = 002 is different I want to create same data into 002

INSERT INTO UserBUMapping (userId, BUId,PracticeAreaId)
    SELECT   UserId = 60338, BUID, PRACTICEAREAID FROM UserBUMapping WHERE UserId = 50326 

insert into UserAccess
      SELECT MODULEID,60338, READACCESS, WRITEACCESS, UPDATEACCESS, DELETEACCESS,APPROVALACCESS 
    FROM USERACCESS WHERE USERID = 50326

The INSERT statement conflicted with the FOREIGN KEY constraint “FK_UserAccess_UserDetails”.

The conflict occurred in database “DataBase Name”, table “dbo.UserDetails”, column ‘UserId’.

CREATE PROCEDURE [dbo].[USP_CLONE_USER]                                                                                        
(                                                                                       
    @CLONE_FROM BIGINT,
    @F_NAME VARCHAR(50),
    @L_NAME VARCHAR(50),
    @EMAIL VARCHAR(50),
    @ENC_EMAIL VARCHAR(200),
    @ENC_PASSWORD VARCHAR(200) ,
    @DATE_OF_JOINING DATETIME                                                                                      
)                                                                                       
AS                                                                                       
BEGIN         


    --INSERTING VALUES INTO USERDETAILS TABLE
    INSERT INTO USERDETAILS (Username, FirstName, LastName, Email, Password, CustomerId, UserStatusId, 
    TimeSheetApplicable, DefaultBUId, DesignationId,  CreatedDate, UniqueId, ReportingPartnerId, ReportingPartnerId2, 
    Version2Access, StopeVersion1Access,DateOfJoining) 
    SELECT  @ENC_EMAIL, @F_NAME, @L_NAME, @EMAIL, @ENC_PASSWORD, CustomerId, UserStatusId, 
    ISNULL(TimeSheetApplicable, 0), DefaultBUId, DesignationId,  GETDATE(), NEWID(), ReportingPartnerId, ReportingPartnerId2, 
    Version2Access, StopeVersion1Access,@DATE_OF_JOINING
    FROM USERDETAILS WHERE UserId = @CLONE_FROM



    DECLARE @NEW_USER_ID BIGINT 
    SET @NEW_USER_ID = @@IDENTITY



    --2. SELECT THE BUSINESS UNIT'S OF USER WHOSE BUSINESS UNIT'S NEEDS TO BE COPIED AND INSERT THE SAME.
    INSERT INTO UserBUMapping 
    SELECT  @NEW_USER_ID, BUID, PRACTICEAREAID FROM UserBUMapping WHERE UserId = @CLONE_FROM   

    --2. SELECT THE ACCESS RIGHTS OF USER WHOSE ACCESS RIGHTS NEEDS TO BE COPIED AND INSERT THE SAME.
    INSERT INTO UserAccess 
    SELECT MODULEID, @NEW_USER_ID, READACCESS, WRITEACCESS, UPDATEACCESS, DELETEACCESS,APPROVALACCESS 
    FROM USERACCESS WHERE USERID = @CLONE_FROM

END 

GO

Advertisement

Answer

create PROCEDURE [dbo].[USP_CLONE_USER]                                                                                        
(                                                                                       
    @CLONE_FROM BIGINT,
    @F_NAME VARCHAR(50),
    @L_NAME VARCHAR(50),
    @EMAIL VARCHAR(50),
    @ENC_EMAIL VARCHAR(200),
    @ENC_PASSWORD VARCHAR(200) ,
    @DATE_OF_JOINING DATETIME                                                                                      
)                                                                                       
AS                                                                                       
BEGIN      

    --INSERTING VALUES INTO USERDETAILS TABLE
    INSERT INTO USERDETAILS (Username, FirstName, LastName, Email, Password, CustomerId, 
     DesignationId,  CreatedDate, UniqueId, ReportingPartnerId, DateOfJoining) 
    SELECT  @ENC_EMAIL, @F_NAME, @L_NAME, @EMAIL, @ENC_PASSWORD, CustomerId, 
     DesignationId,  GETDATE(), NEWID(), ReportingPartnerId, ReportingPartnerId2, 
    Version2Access, StopeVersion1Access,@DATE_OF_JOINING,IsExempted
    FROM USERDETAILS WHERE UserId = @CLONE_FROM

    DECLARE @NEW_USER_ID BIGINT 

    SET @NEW_USER_ID = ***IDENT_CURRENT('UserDetails')***

    --2. SELECT THE BUSINESS UNIT'S OF USER WHOSE BUSINESS UNIT'S NEEDS TO BE COPIED AND INSERT THE SAME.
    INSERT INTO UserBUMapping (UserId,BUId,PracticeAreaId)
   SELECT  @NEW_USER_ID, BUID, PRACTICEAREAID FROM UserBUMapping WHERE UserId = @CLONE_FROM   

   --2. SELECT THE ACCESS RIGHTS OF USER WHOSE ACCESS RIGHTS NEEDS TO BE COPIED AND INSERT THE SAME.
    INSERT INTO UserAccess (MODULEID, UserId, READACCESS)
   SELECT MODULEID, @NEW_USER_ID, READACCESS,
    FROM USERACCESS WHERE USERID = @CLONE_FROM
END 
GO
User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement