What can I use instead of “Set Identity Insert On”?

I have stored procedure in SQL Server. In this procedure, I delete duplicate records and insert one unique records to table. When I insert new unique record I am using below script.

    SET IDENTITY_INSERT tbl_personnel_info ON
    
    INSERT INTO tbl_personnel_info (pk_id, first_name, last_name, department, age, phone_number)

    SELECT pk_id, first_name, last_name, department, age, phone_number
    FROM #Unique

    SET IDENTITY_INSERT tbl_personnel_info Off

Everthing is okey with this script but in the production SET IDENTITY_INSERT command needs to ALTER permission. Giving this permission should be dangerous so I can’t give this permission. Also I must insert old pk_id instead of new. How can I do this without SET IDENTITY_INSERT command.

For example I have those records.

first_name	last_name	department	age	phone_number
John	Doe	IT	21	XXX
John	Doe	Finance	22	YYY
John	Doe	HR	23	ZZZ

And the record i want is

first_name	last_name	department	age	phone_number
John	Doe	IT	23	YYY

I also have my wanted record in the #Unique table. I want to delete 3 records and add record which is in the unique table.

Answer

I still believe that you have a bit of an xy problem here, and you would be better off preventing the duplicates at source rather than having a clean up procedure that needs to be regularly run by people other than the sa, but to actually answer your question one option would be not not delete the records you want to retain.

If you generate your #Unique table before you do the delete, then you can simply use something like:

SET XACT_ABORT ON;
BEGIN TRANSACTION;

UPDATE  p WITH (UPDLOCK, SERIALIZABLE)
SET     first_name = u.first_name,
        last_name = u.last_name,
        department = u.department,
        age = u.age,
        phone_number = u.phone_number
FROM    tbl_personnel_info  AS p
        INNER JOIN #Unique AS u
            ON u.pk_id = p.pk_id
WHERE   NOT EXISTS
        (   SELECT u.first_name, u.last_name, u.department, u.age. u.phone_number
            INTERSECT
            SELECT p.first_name, p.last_name, p.department, p.age. p.phone_number
        );

DELETE p
FROM   tbl_personnel_info AS p
WHERE  NOT EXISTS (SELECT 1 FROM #Unique AS u WHERE u.pk_id = p.pk_id);


COMMIT TRANSACTION;

This will update the records you want to retain and were originally planning to re-insert(but only if there is a value that needs to be updated), then only delete any records that don’t exist in your temp table.

One big issue you may face here is foreign keys, you would presumably also need to tidy up any records related to the records you are deleting? This is another reason why you would be much better off preventing the duplicates at source and doing one single clear up (therefore stored procedure not required).

For a bit of an analogy, you have a hole in your boat and your current approach is to grab a bucket and keep scooping water over board, which you’ll be doing forever and the hole will only get bigger. The hole is as small as it will ever be right now – so now is the best time to plug it.