SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE [dbo].[p_cpl_duplicate_row]
	@table_name SYSNAME,
	@primary_key_field_name SYSNAME,
	@source_id INT,
	@destination_id INT 
AS
	SET NOCOUNT ON;

	DECLARE @view_complete_name SYSNAME 
	
	DECLARE @sql NVARCHAR(MAX)
	
	EXEC p_sys_create_view_without_rowver_computed @schema_name = 'dbo'
												  ,@table_name = @table_name
												  ,@view_complete_name = @view_complete_name OUT 

	SET @sql =	' SELECT * ' + CHAR(13) + 
				' INTO #tmp' + CHAR(13) +
				' FROM ' + @view_complete_name + CHAR(13) +
				' WHERE ' +  @primary_key_field_name + ' = ' + CONVERT(VARCHAR, @source_id) + CHAR(13) + CHAR(13) + 
				' UPDATE #tmp set ' + @primary_key_field_name + ' = '  + CONVERT(VARCHAR, @destination_id) + CHAR(13) + CHAR(13) + 
				' INSERT INTO ' +  @view_complete_name + CHAR(13) +
				' SELECT * ' + CHAR(13) +
				' FROM #tmp' + CHAR(13)

	--PRINT @sql

	EXEC sp_executesql @sql

	SET @sql = 'drop view ' + @view_complete_name

	EXEC sp_executesql @sql
/*
select * from pol_cpl
delete from pol_cpl where pcpl_policy_situation_id = 50000012
EXEC p_cpl_duplicate_row @table_name = 'pol_cpl'
						,@primary_key_field_name = 'pcpl_policy_situation_id'
						,@source_id = 50000011
						,@destination_id = 50000012

EXEC p_cpl_duplicate_row @table_name = 'act_cpl'
						,@primary_key_field_name = 'acpl_actor_id'
						,@source_id = 1
						,@destination_id = 2

EXEC p_cpl_duplicate_row @table_name = 'ris_cpl'
						,@primary_key_field_name = 'rcpl_risk_situation_id'
						,@source_id = 1
						,@destination_id = 2

EXEC p_cpl_duplicate_row @table_name = 'claim_cpl'
						,@primary_key_field_name = 'ccpl_claim_id'
						,@source_id = 1
						,@destination_id = 2

EXEC p_cpl_duplicate_row @table_name = 'claim_actors_cpl'
						,@primary_key_field_name = 'clactcpl_claim_id'
						,@source_id = 1
						,@destination_id = 2
*/

GO
