title: SQL Server : Insert cursor within stored procedure
body: <p>I'm new to SQL Server and was given a task where I have to use cursor to duplicate records for Phone Link table. Other tables I have managed to duplicate without needing to use cursor. However I have primary key constraint problem with Phone Link table. Will someone please assist me? Thank you. Following is my stored procedure. Intake parameter is comp_companyid. My requirement is to duplicate every column into the same table and generate new primary key &amp; foreign key.</p>
  
  <p>P/S:</p>
  
  <ul>
  <li>EntityID = 5 (Company)</li>
  <li>EntityID = 13 (Person)</li>
  </ul>
  
  <p>As I have person phone &amp; person phone link to add on afterwards as well. But I just need to solve this first then I can refer to this when I'm doing person phone.</p>
  
  <p>Also, one company can have multiple person, address.</p>
  
  <pre><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
  
      -- Add Person_Link Without Type
      INSERT INTO Person_Link
      (
          PeLi_PersonLinkId, PeLi_PersonId, PeLi_CompanyID, PeLi_CreatedBy, PeLi_CreatedDate, PeLi_UpdatedBy,
          PeLi_UpdatedDate, PeLi_TimeStamp
      )
          SELECT 
              @PersonLinkId, @PersonId, @CompanyId, PeLi_CreatedBy, 
              PeLi_CreatedDate, '1', GETDATE(), PeLi_TimeStamp
          FROM 
              Person_Link
          WHERE 
              PeLi_CompanyID = @comp_companyid
  
      -- Add Person
      INSERT INTO Person
      (
          Pers_PersonId, Pers_CompanyId, Pers_PrimaryUserId, Pers_FirstName, pers_SecTerr, Pers_CreatedBy,
          Pers_CreatedDate, Pers_UpdatedBy, Pers_UpdatedDate, Pers_TimeStamp
      )
          SELECT 
              @PersonId, @companyId, Pers_PrimaryUserId, Pers_FirstName, 
              pers_SecTerr, Pers_CreatedBy,
              Pers_CreatedDate, '1', GETDATE(), Pers_TimeStamp
          FROM 
              Person
          WHERE 
              Pers_CompanyId = @comp_companyid
  
      -- Add Address_Link
      INSERT INTO Address_Link
      (
          AdLi_AddressLinkId, AdLi_AddressId, AdLi_CompanyID, AdLi_CreatedBy, AdLi_CreatedDate,
          AdLi_UpdatedBy, AdLi_UpdatedDate, AdLi_TimeStamp, AdLi_Type
      )
          SELECT 
              @AddressLinkId, @AddressId, @companyId, AdLi_CreatedBy, 
              AdLi_CreatedDate, '1', GETDATE(), AdLi_TimeStamp, AdLi_Type
          FROM 
              Address_Link
          WHERE  
              AdLi_CompanyID = @comp_companyid
  
  
      -- Add Address 
      INSERT INTO [Address]
      (
          Addr_AddressId, Addr_Address1, Addr_Address2, Addr_Address3, Addr_Address4, addr_postcode,
          Addr_CreatedBy, Addr_CreatedDate, Addr_UpdatedBy, Addr_UpdatedDate, Addr_TimeStamp
      )
      SELECT @AddressId, Addr_Address1, Addr_Address2, Addr_Address3, Addr_Address4, addr_postcode,
             Addr_CreatedBy, Addr_CreatedDate, '1', GETDATE(), Addr_TimeStamp
      FROM Address
      INNER JOIN Address_Link
      ON Addr_AddressId = AdLi_AddressId
      AND AdLi_CompanyID = @comp_companyid
  
  
  
      -- Add PhoneLink
      -- Declare Variables
  
  
      DECLARE @c_PLink_LinkID NVARCHAR(30)
      DECLARE @c_PLink_PhoneId NVARCHAR(30)
      DECLARE @c_PLink_CreatedBy NVARCHAR(30)
      DECLARE @c_PLink_CreatedDate NVARCHAR(30)
      DECLARE @c_PLink_UpdatedDate NVARCHAR(30)
      DECLARE @c_PLink_TimeStamp NVARCHAR(30)
      DECLARE @c_PLink_EntityID NVARCHAR(30)
      DECLARE @c_PLink_RecordID NVARCHAR(30)
      DECLARE @c_PLink_Type NVARCHAR(30)
      DECLARE @c_Phon_PhoneId NVARCHAR(30)
  
          EXEC @c_PLink_LinkID = crm_next_id 10208
          EXEC @c_PLink_PhoneId = crm_next_id 14
  
  
      --Declare Cursor
      DECLARE @getPLID CURSOR
      SET @getPLID= CURSOR FOR
      SELECT PLink_LinkID, PLink_PhoneId, PLink_CreatedBy, PLink_CreatedDate, PLink_UpdatedDate, PLink_TimeStamp,
      PLink_EntityID, PLink_RecordID, PLink_Type
      FROM PhoneLink
      WHERE PLink_EntityID = 5
      AND PLink_RecordID = @Comp_CompanyId
  
      --Open Cursor &amp; fetch 1st row into variables
      OPEN @getPLID
      FETCH NEXT FROM @getPLID INTO @c_PLink_LinkID, @c_PLink_PhoneId, @c_PLink_CreatedBy,
      @c_PLink_CreatedDate, @c_PLink_UpdatedDate, @c_PLink_TimeStamp, @c_PLink_EntityID, @c_PLink_RecordID, @c_PLink_Type
  
      --Check for a new row
      WHILE @@FETCH_STATUS = 0
      BEGIN
  
          INSERT INTO PhoneLink
          (
              PLink_LinkID, PLink_PhoneId, PLink_CreatedBy, PLink_CreatedDate, PLink_UpdatedDate, PLink_TimeStamp,
              PLink_EntityID, PLink_RecordID, PLink_Type
          )
  
          VALUES
          (
              @c_PLink_LinkID, @c_PLink_PhoneId, @c_PLink_CreatedBy, @c_PLink_CreatedDate, @c_PLink_UpdatedDate, @c_PLink_TimeStamp,
              @c_PLink_EntityID, @c_PLink_RecordID, @c_PLink_Type
          )
  
  
      --Get next available row into variables
      FETCH NEXT FROM @getPLID INTO @c_PLink_LinkID, @c_PLink_PhoneId, @c_PLink_CreatedBy,
      @c_PLink_CreatedDate, @c_PLink_UpdatedDate, @c_PLink_TimeStamp, @c_PLink_EntityID, @c_PLink_RecordID, @c_PLink_Type
  
      END
  
      CLOSE @getPLID
      DEALLOCATE @getPLID
  
  
  
      -- Add Company Phone
      INSERT INTO Phone
      (
          Phon_PhoneId, Phon_Number, Phon_CreatedBy, Phon_CreatedDate, Phon_UpdatedBy, Phon_UpdatedDate, Phon_TimeStamp
      )
      SELECT @PhoneID, Phon_Number, Phon_CreatedBy, Phon_CreatedDate, '1', GETDATE(), Phon_TimeStamp
      FROM Phone
      INNER JOIN PhoneLink
      ON Phon_PhoneId = PLink_PhoneId
      AND PLink_EntityID = 5
      AND PLink_RecordID = @Comp_CompanyId
  
  
  END
  </code></pre>
  
  <p>Appreciate if you can tell me which part I've done wrong. Thank you for your time and effort in advance!</p>