Md copy wizard content

Purpose

This SP copies wizard content from an existing wizard into a new one. The new wizard needs to exist, so the common procedure is to create a new empty wizard, and grab the key. Grab the old wizard key and call the stored procedure with the proper _add_user name

-- "md_copy_wizard_content"
-- Author: Silviu Sterian
-- Created: 12/04/2007
-- Description: Copies wizard pages, buttons, steps and all other metadata  from a preexisting wizard into another existing wizard (usually a newly created, empty one)

USE [netFORUM2]
GO
/****** Object:  StoredProcedure [dbo].[md_copy_wizard_content]    Script Date: 06/17/2008 15:43:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:	Silviu Sterian
-- Created: 12/04/2007
-- Description: Copies wizard pages, buttons, steps and all other metadata  from a preexisting wizard into another existing wizard (usually a newly created, empty one)
-- =============================================
CREATE PROCEDURE [dbo].[md_copy_wizard_content]
 
	@New_wizkey uniqueidentifier, --the new wizard into which we want to copy metadata
	@Existing_wizkey uniqueidentifier, --old wizard key
	@adduser nvarchar(40) -- add user that will be used to set all _add_user fields; _add_Date will be current; change user and change date will de set to null
 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
declare @formkey uniqueidentifier
declare @newguid uniqueidentifier
 
 
CREATE TABLE #csv (oldkey uniqueidentifier NOT NULL, newkey  uniqueidentifier NOT NULL)
 
 
BEGIN TRANSACTION
 
 
INSERT #csv (oldkey,newkey) select wzs_key,newid() from md_wizard_step where wzs_wiz_key = @Existing_wizkey
 
SELECT * INTO #temp1 from md_wizard_form where wzf_wiz_key = @Existing_wizkey
 
SELECT * INTO #temp4 from md_wizard_step where wzs_wiz_key = @Existing_wizkey
UPDATE #temp4  set wzs_key =(select b.newkey from #csv b where b.oldkey = wzs_key),wzs_wiz_key = @New_wizkey,wzs_add_date = getdate(),wzs_add_user = @adduser,wzs_change_user = null,wzs_change_date = null
INSERT INTO md_wizard_step SELECT * FROM #temp4
 
declare form_cursor cursor FOR
SELECT wzf_key FROM #temp1
 
SELECT top 1 * INTO #temp2 from md_wizard_form_button  where wzb_wzf_key = @formkey		
TRUNCATE TABLE #temp2
 
open form_cursor
 
fetch next FROM form_cursor INTO @formkey
 
while @@FETCH_STATUS = 0
begin
	INSERT INTO #temp2 select * from md_wizard_form_button  where wzb_wzf_key = @formkey
 
	SET @newguid = newid()	
	INSERT INTO md_wizard_form (wzf_key,wzf_wiz_key,wzf_wzs_key,wzf_progress_title,wzf_form_title,wzf_form_header,wzf_progress_exclude,wzf_progress_icon,wzf_order,wzf_content_type,
		wzf_dyn_key,wzf_dyc_key,wzf_show_all_child,wzf_control_file,wzf_dyn_dependson_key,wzf_form_parameter,wzf_html,wzf_add_user,wzf_add_date,wzf_change_user,wzf_change_date,wzf_delete_flag,
		wzf_entity_key) 
	SELECT @newguid,@New_wizkey,(SELECT newkey FROM #csv where oldkey=wzf_wzs_key),wzf_progress_title,wzf_form_title,wzf_form_header,wzf_progress_exclude,wzf_progress_icon,wzf_order,wzf_content_type,wzf_dyn_key,
		wzf_dyc_key,wzf_show_all_child,wzf_control_file,wzf_dyn_dependson_key,wzf_form_parameter,wzf_html,@adduser,getdate(),NULL,NULL,wzf_delete_flag,wzf_entity_key 
		FROM #temp1	where wzf_key = @formkey
 
	UPDATE #temp2 set wzb_key =newid(),wzb_wzf_key = @newguid,wzb_add_date = getdate(),wzb_add_user = @adduser,wzb_change_user = null,wzb_change_date = null
	INSERT INTO md_wizard_form_button SELECT * FROM #temp2
	TRUNCATE TABLE #temp2	
 
	fetch next FROM form_cursor INTO @formkey
end
 
close form_cursor
deallocate form_cursor
 
DROP TABLE #temp1 
DROP TABLE #temp2		
 
SELECT * INTO #temp3  from md_wizard_progress md_wizard where wzp_wiz_key = @Existing_wizkey
UPDATE #temp3 set wzp_key =newid(),wzp_wiz_key=@New_wizkey,wzp_add_date = getdate(),wzp_add_user = @adduser,wzp_change_user = null,wzp_change_date = null
INSERT INTO md_wizard_progress SELECT * FROM #temp3
 
DROP TABLE #temp3
DROP TABLE #temp4
DROP TABLE #csv
 
 
 
IF @@ERROR <> 0
 begin    
    ROLLBACK   
    raiserror ('Error when running', 16, 1)
    RETURN
 end
else
	COMMIT
 
END

Parameters

• @New_wizkey (uniqueidentifier). The new wizard key (md_wizard.wiz_key) into which we want to copy metadata
• @Existing_wizkey (uniqueidentifier). Existing wizard key
• @adduser (nvarchar(40)). Add user that will be used to set all _add_user fields; _add_date will be current; change user and change date will be set to null

Where Used

It will mostly be used when we need to duplicate a wizard. A common situation is moving a iWeb wizard to eWeb. Most likely the eweb wizard will need some small changes, while we want the iWeb to remain intact.

The sp saves the time necessary to move all pages "manually" and eliminates the risk of forgetting to move metadata, like a page, a wizard step or visibility condition.