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
x
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