sql - 已经插入的数据替换到另一个 UserId

Question

在我的业务单元表中，FK 是 UserId

例如：UserId = 001，BusinessUnit = 班加罗尔

例如：UserId = 001，BusinessUnit = Hyd ....

现在，我需要 UserId = 002 不同我想在 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

INSERT 语句与 FOREIGN KEY 约束“FK_UserAccess_UserDetails”冲突。

冲突发生在数据库“DataBase Name”、表“dbo.UserDetails”、列“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

标签： sqlsql-server-2008

Answer 1

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