CODEWITHSUNDEEP
sqlsql-server
Desmond Chau
Desmond Chau

Reputation: 13

SQL Server - Duplicate Multiple Tables with Cursors

I was given a task to use cursor to duplicate tables. Say now I have 3 tables to duplicate from: Company, Person_Link & Person. It's a 'One to Many' relationships as one company can have multiple persons.

This is my code:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[DuplicateCompanyInfo]
    @Comp_CompanyId NVARCHAR(80)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @CompanyID NVARCHAR(30),
            @PersonID NVARCHAR(30),
            @PersonLinkID NVARCHAR(30),
            @AddressLinkID NVARCHAR(30),
            @AddressID NVARCHAR(30),
            @PhoneLinkID NVARCHAR(30),
            @PhoneID NVARCHAR(30)

    EXEC @companyId = crm_next_id 5
    EXEC @PersonId = crm_next_id 13
    EXEC @PersonLinkId = crm_next_id 31
    EXEC @AddressLinkId = crm_next_id 21
    EXEC @AddressId = crm_next_id 1
    EXEC @PhoneLinkId = crm_next_id 10208
    EXEC @PhoneId = crm_next_id 14

    -- Add Company
    INSERT INTO Company
    (
        Comp_CompanyId, Comp_PrimaryPersonId, Comp_PrimaryAddressId, Comp_Name, Comp_Type, Comp_Status, Comp_CreatedBy,
        Comp_CreatedDate, Comp_UpdatedBy, Comp_UpdatedDate, Comp_TimeStamp, Comp_SecTerr, Comp_WebSite
    )
    SELECT  @companyId, @PersonId, @AddressId, Comp_Name, Comp_Type, Comp_Status, Comp_CreatedBy,
            Comp_CreatedDate, '1', GETDATE(), Comp_TimeStamp, Comp_SecTerr, Comp_WebSite
        
    FROM Company
    WHERE Comp_CompanyId = @comp_companyid
    

------- PersonLink Cursor ---------------------------------------------
    -- Declare Variables
    DECLARE @c_PeLi_PersonLinkId NVARCHAR(30)
    DECLARE @c_PeLi_PersonId NVARCHAR(30)
    DECLARE @c_PeLi_CompanyID NVARCHAR(30)
    DECLARE @c_PeLi_CreatedBy NVARCHAR(30)
    DECLARE @c_PeLi_CreatedDate NVARCHAR(30)
    DECLARE @c_PeLi_UpdatedBy NVARCHAR(30)
    DECLARE @c_PeLi_UpdatedDate NVARCHAR(30)
    DECLARE @c_PeLi_TimeStamp NVARCHAR(30)


    --Declare Cursor
    DECLARE @getPeLiID CURSOR
    SET @getPeLiID= CURSOR FOR
    SELECT PeLi_PersonLinkId, PeLi_PersonId, PeLi_CompanyID, PeLi_CreatedBy, PeLi_CreatedDate, PeLi_UpdatedBy,
        PeLi_UpdatedDate, PeLi_TimeStamp
    FROM Person_Link
    INNER JOIN Person
    ON PeLi_PersonId = Pers_PersonId
    AND PeLi_CompanyID = @comp_companyid
    
    --Open Cursor & fetch 1st row into variables
    OPEN @getPeLiID
    FETCH NEXT FROM @getPeLiID INTO @c_PeLi_PersonLinkId, @c_PeLi_PersonId, @c_PeLi_CompanyID, @c_PeLi_CreatedBy,
                                  @c_PeLi_CreatedDate, @c_PeLi_UpdatedBy, @c_PeLi_UpdatedDate, @c_PeLi_TimeStamp
    
    
    --Fetch successful
    --Check for a new row
    WHILE @@FETCH_STATUS = 0
    BEGIN

        --EXEC @PersonLinkId = crm_next_id 31


    INSERT INTO Person_Link
    (
        PeLi_PersonLinkId, PeLi_PersonId, PeLi_CompanyID, PeLi_CreatedBy, PeLi_CreatedDate, PeLi_UpdatedBy,
        PeLi_UpdatedDate, PeLi_TimeStamp
    )
    VALUES
    (
        @PersonLinkId, @PersonId, @CompanyId, @c_PeLi_CreatedBy, @c_PeLi_CreatedDate, '1', GETDATE(), @c_PeLi_TimeStamp
    )


    --Get next available row into variables
    FETCH NEXT FROM @getPeLiID INTO @c_PeLi_PersonLinkId, @c_PeLi_PersonId, @c_PeLi_CompanyID, @c_PeLi_CreatedBy,
                                  @c_PeLi_CreatedDate, @c_PeLi_UpdatedBy, @c_PeLi_UpdatedDate, @c_PeLi_TimeStamp
    
    END
    
    CLOSE @getPeLiID
    DEALLOCATE @getPeLiID


------- Person Cursor -------------------------------------------------
    -- Declare Variables
    DECLARE @c_Pers_PersonId NVARCHAR(30)
    DECLARE @c_Pers_CompanyId NVARCHAR(30)
    DECLARE @c_Pers_PrimaryUserId NVARCHAR(30)
    DECLARE @c_Pers_FirstName NVARCHAR(30)
    DECLARE @c_Pers_SecTerr NVARCHAR(30)
    DECLARE @c_Pers_CreatedBy NVARCHAR(30)
    DECLARE @c_Pers_CreatedDate NVARCHAR(30)
    DECLARE @c_Pers_UpdatedBy NVARCHAR(30)
    DECLARE @c_Pers_UpdatedDate NVARCHAR(30)
    DECLARE @c_Pers_TimeStamp NVARCHAR(30)
    

    --Declare Cursor
    DECLARE @getPersID CURSOR
    SET @getPersID= CURSOR FOR
    SELECT Pers_PersonId, Pers_CompanyId, Pers_PrimaryUserId, Pers_FirstName, Pers_SecTerr, Pers_CreatedBy,
           Pers_CreatedDate, Pers_UpdatedBy, Pers_UpdatedDate, Pers_TimeStamp
    FROM Person
    INNER JOIN Person_Link
    ON Pers_PersonId = PeLi_PersonId
    AND PeLi_CompanyID = @comp_companyid    
    
    
    --Open Cursor & fetch 1st row into variables
    OPEN @getPersID
    FETCH NEXT FROM @getPersID INTO @c_Pers_PersonId, @c_Pers_CompanyId, @c_Pers_PrimaryUserId, @c_Pers_FirstName,
                                  @c_Pers_SecTerr, @c_Pers_CreatedBy, @c_Pers_CreatedDate, @c_Pers_UpdatedBy,
                                  @c_Pers_UpdatedDate, @c_Pers_TimeStamp
    
    
    --Fetch successful
    --Check for a new row
    WHILE @@FETCH_STATUS = 0
    BEGIN

    --EXEC @PersonId = crm_next_id 13


    INSERT INTO Person
    (
        Pers_PersonId, Pers_CompanyId, Pers_PrimaryUserId, Pers_FirstName, Pers_SecTerr, Pers_CreatedBy,
        Pers_CreatedDate, Pers_UpdatedBy, Pers_UpdatedDate, Pers_TimeStamp
    )
    VALUES
    (
        @PersonId, @companyId, @c_Pers_PrimaryUserId, @c_Pers_FirstName, @c_Pers_SecTerr, @c_Pers_CreatedBy,
        @c_Pers_CreatedDate, '1', GETDATE(), @c_Pers_TimeStamp
    )

        
    --Get next available row into variables
    FETCH NEXT FROM @getPersID INTO @c_Pers_PersonId, @c_Pers_CompanyId, @c_Pers_PrimaryUserId, @c_Pers_FirstName,
                                  @c_Pers_SecTerr, @c_Pers_CreatedBy, @c_Pers_CreatedDate, @c_Pers_UpdatedBy,
                                  @c_Pers_UpdatedDate, @c_Pers_TimeStamp
    
    END
    
    CLOSE @getPersID
    DEALLOCATE @getPersID

This is my table structure:

Company Table:

comp_companyid | comp_primarypersonid | comp_primaryaddressid | comp_name
-------------------------------------------------------------------------
2              | 2                    | 2                     | company 2
3              | 3                    | 3                     | company 3

Person Table:

pers_personid  | pers_companyid       | pers_primaryaddressid | pers_name
-------------------------------------------------------------------------
2              | 2                    | 2                     | person 2
3              | 3                    | 3                     | person 3
4              | 2                    | 2                     | person 4

Person Link Table:

peli_personlinkid |    peli_personid     | peli_companyid
-------------------------------------------------------------------------
2                 | 2                    | 2                  
3                 | 3                    | 3

I have no problem executing the stored procedure when there's only 1 person belonged to 1 company, but when the company has 2 person or above, the 2nd person's row won't be duplicated, only the first one.

The error message will pop out:

Msg 2627, Level 14, State 1, Procedure DuplicateCompanyInfo, Line 80
Violation of PRIMARY KEY constraint 'PK__Person_L__BCD268642CF2ADDF'. Cannot insert duplicate key in object 'dbo.Person_Link'.

Msg 2627, Level 14, State 1, Procedure DuplicateCompanyInfo, Line 141
Violation of PRIMARY KEY constraint 'PK__Person__381A5AC529221CFB'. Cannot insert duplicate key in object 'dbo.Person'.

I can fix this by removing the comment --EXEC @PersonLinkId = crm_next_id 31 & --EXEC @PersonID = crm_next_id 13 after cursor began so that they will generate a new ID each time duplicated. But it will cause the Comp_PrimaryPersonID & Pers_PersonID & PeLi_PersonID not tallied because each of them will generate own new ID based on their table duplication instead of applying each other's foreign keys.

Please help or guide me through! Thanks in advance for your time & effort! Desmond

Upvotes: 0

Views: 324

Answers (1)

Imran
Imran

Reputation: 49

Sorry don't have access to SQL-Server right now.

I cannot see your table definitions where you are inserting data. But from the error it looks like your table definition is not correct. i.e. Your primary key selection is not correct for the person and person_link tables. Your currect primary key should be a foreign key.

Second, why cursors? if you just need to duplicate the data from one set of tables to the other why not just use query? is there any specific reason to use cursor?

UPDATE: From your updated table structure, the primary and foreign keys are not clear, but looks like comp_companyid is primary key in your company table which is linked as foreign key in Person (pers_companyid) and Person_Link (peli_companyid) tables.

Now if structure of your tables are like this:

Company Table: comp_companyid (PK)

Person Table: pers_personid (PK) and pers_companyid (FK)

Person Link Table: peli_personlinkid (PK) and peli_personid (FK), peli_companyid (FK)

I do not see any duplication problem unless you are inserting duplicates in the PK columns.

Now after carefully looking at your code you are fetching peli_personlinkid into @c_PeLi_PersonLinkId variable.

FETCH NEXT FROM @getPeLiID INTO @c_PeLi_PersonLinkId, @c_PeLi_PersonId, @c_PeLi_CompanyID, @c_PeLi_CreatedBy, @c_PeLi_CreatedDate, @c_PeLi_UpdatedBy, @c_PeLi_UpdatedDate, @c_PeLi_TimeStamp

But when you are inserting data you are not inserting this value, instead you are inserting @PersonLinkId in the primary key. As this value is coming from another stored procedure, (I do not know what this stored procedure is doing but looks like generating next id and returning). Which you are executing at the start of the procedure

EXEC @PersonLinkId = crm_next_id 31

So, in the loop you keep on inserting this value for every person_link. Which is of course violation of PK constraint, if there will be more than one person_link in the table. And exact same issue is with the Person table as well.

That is why you are getting primary key violation errors.

Instead of inserting this value everytime, you should insert the value retrieved from @c_PeLi_PersonLinkId which fetching row.

Hope this will help now and you will be able to resolve the issue.

Honestly, I still believe you can talk to your senior and can easily avoid cursors in this situation and you can achieve all this with simple insert and select statements, and that will be far better and faster as well. But if you are not in a position to replace cursors, you can just fix the stored procedure as identified and suggested above.

Upvotes: 1

Related Questions