Sql Server : Insert Cursor Within Stored Procedure

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 cur

Solution 1:

Not sure what all of inserted columns mean but if you are inserting many rows you need to generate another unique id for each row. So you have to move ID generation code into cursor body instead of placing it before cursor declaration and call this generator each iteration.

...
WHILE @@FETCH_STATUS =0BEGINEXEC@c_PLink_LinkID = crm_next_id 10208EXEC@c_PLink_PhoneId = crm_next_id 14INSERTINTO 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
    )


    FETCH NEXT FROM@getPLIDINTO@c_PLink_CreatedBy, @c_PLink_CreatedDate, @c_PLink_UpdatedDate, 
      @c_PLink_TimeStamp, @c_PLink_EntityID, @c_PLink_RecordID, @c_PLink_Type
END

If PhoneID needs to be generated too - you have to place it inside your cursor too. Also remove PLink_LinkID and PLink_PhoneId from cursors's select since you don't need original values. And, of course, remove'em from fetch lists. Just as shown above.

But, if my assumptions are correct then you are going a bit wrong way. If PhoneLink table is a table that links some Phones to newly created Entity/Company, then you need to copy Phone first (and generate ID for that record) and after that - to build a link between new Phone_ID and new Company_ID. Now, if you always have only one Phone per Company (which would be a bit strange) then you don't need cursor. But if you can have several phones per company then you need to generate several new Phone_IDs. Which means, you need to iterate through phones and links instead of iterating links only. And the code should look like:

...
WHILE @@FETCH_STATUS =0BEGINEXEC@c_PLink_LinkID = crm_next_id 10208EXEC@c_PLink_PhoneId = crm_next_id 14INSERTINTO 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
      WHERE Phon_PhoneId =@Old_Phone_ID

    INSERTINTO 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
    )


    FETCH NEXT FROM@getPLIDINTO@Old_Phone_ID, @c_PLink_CreatedBy, @c_PLink_CreatedDate, @c_PLink_UpdatedDate, 
      @c_PLink_TimeStamp, @c_PLink_EntityID, @c_PLink_RecordID, @c_PLink_Type
END

Note, I returned @Old_Phone_ID into fetch list - so you could locate copied phone by id. And last insert-select-phone is no longer needed in this case (and actually is incorrect in many phones case 'cause it's inserting scalar @PhoneID, same for all inserted rows).