CODEWITHSUNDEEP
sql-server-2008variablesinsert
CodeMonkey
CodeMonkey

Reputation: 1107

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

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';

Upvotes: 4

Views: 13360

Answers (1)

marc_s
marc_s

Reputation: 754298

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!

Upvotes: 5

Related Questions