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

Question

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

I do not want to delete rows (stored for historical reasons). I get a PK violation while 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'; 

Answer 1

Your approach does not work, because you evaluate 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 will need to use something like this to do your job:

 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 necessary values, plus it uses ROW_NUMBER() to generate consecutive numbers (from 1 to the number of rows selected for @plant = 'IM17' ).

When you add this RowNum to the previous maximum value, and now do not insert anything into this target table right now - then you have a chance of success!