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