How to increment variable for each row in a insert/select

Question

I have a table with the primary key event_id. I need to change the Plant from IM17 to SG18.

I do not want to delete the rows (being kept for historical reasons). I get a PK violation when executing the following SQL.

DECLARE @plant CHAR(4)
DECLARE @l_event_id INT

SELECT @plant = 'SG18'
SET @l_event_id = (SELECT MAX(cast(event_id as int)) FROM dbo.event_header) 

INSERT INTO dbo.event_header (
            event_id, scenario_name, actor_code, method_code,
            SAP_plant_code, object_id,serial_no
            )
    SELECT @l_event_id + 1 , eh.scenario_name, eh.actor_code,
           eh.method_code, @plant, eh.object_id, eh.serial_no
    FROM dbo.event_header eh
    WHERE eh.SAP_plant_code = 'IM17';

Solution

Your approach doesn't work, since you evaluate the MAX(cast(event_id as int)) only once - and then try to insert all n new rows with the same value for event_id....

You'll need to use something like this to get your job done:

DECLARE @plant CHAR(4) = 'SG18'

DECLARE @l_event_id INT
SELECT @l_event_id = MAX(CAST(event_id AS INT)) FROM dbo.event_header) 

;WITH CTE AS
(
    SELECT 
        eh.scenario_name, 
        eh.actor_code,
        eh.method_code, 
        eh.object_id, 
        eh.serial_no,
        RowNum = ROW_NUMBER() OVER (ORDER BY eh.serial_no)
    FROM dbo.event_header eh
    WHERE eh.SAP_plant_code = 'IM17';
)
INSERT INTO 
    dbo.event_header (event_id, scenario_name, actor_code, method_code, SAP_plant_code, object_id,serial_no)
    SELECT
        @l_event_id + RowNum,
        scenario_name, actor_code, method_code, 
        @plant, 
            object_id, serial_no
    FROM
        CTE  

Basically, this CTE (Common Table Expression) gets all the values you need, plus it uses ROW_NUMBER() to generate sequential numbers (from 1 to the number of rows selected for @plant = 'IM17').

When you add that RowNum to the previous max value, and nothing else is inserted data into that target table right now - then you stand a chance of success!

Comments

[marc_s]

It would be really easy if event_Id were a INT IDENTITY column - then SQL Server would handle all those nitty gritty details of automagically updating the id value......

[pkmiec]

Is the table dbo.event_header a "busy" table? (are there many users/transactions that insert into that table?)

[CodeMonkey]

@marc_s yes that would be great, but it is a legacy DB and the PK is varchar(20) :(

[CodeMonkey]

Thank you, this code snippet is going in my personal repository.