0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

Pleasanter Tips:期限付きテーブルから記録テーブルに変える(その逆も)

Last updated at Posted at 2023-08-19

はじめに

Pleasanterには期限付きテーブルまたは記録テーブルを選択してテーブルを作成します。しかし作成後にテーブル種別を変えたくなる場合が有ります。
通常はデータをエクスポート後に新たに作成したテーブルにインポートする方法を取りますが、テーブルの関連性を維持しないなどそのまま変える事ができればと思う事が有ります。
今回はテーブルの情報をそのままにして期限付きテーブルから記録テーブルに(その逆)変える方法を行ってみます。

注意事項

1. 今回の方法は直接データを変える方法です。従って以下の処理を行う事で不具合を起こす場合があるのでご注意ください。
2. 事前にバックアップもお願いします。
3. 今回のスクリプトはEnterprise Edition時に追加した項目には対応していません。環境に応じて変更してください。

検証環境

Pleasanter 1.3.44.0
SQL-Server 15.0.2

処理概要

Pleasanterではデータを保管する場合下記のテーブルが関係します。

  • ResultsまたはIssues
  • Items
  • Sites

ResultsまたはIssues

データは期限付きテーブル(Issues)または 記録テーブル(Results)に格納されます。
違いは下記の2点です。

  • IDに関係するフィールドはResultIdまたはIssueIdになる。
  • Issuesには開始(StartTime)、完了(CompletionTime)、作業量(WorkValue)、進捗率(ProgressRate)のフィールドが存在する。
    テーブル種別を変える場合はResultsからIssues(またはその逆)にデータを移動する必要が有ります。
    ResultsからIssuesに移動する場合、デフォルト値としてStartTime=NULL、WorkValue=0、ProgressRate=0をセットしますが、CompletionTimeにはNULLをセットする事ができません。その為に何らかの日付をセットする必要が有ります。

Items

ItemsにはResults及びIssuesのデータの共通データが格納されています。
テーブル種別がReferenceTypeにセットされています。テーブルを変更する際は該当するデータ種別に変える必要が有ります。

Sites

Sitesにはテーブルの設計情報が格納されています。
Itemsと同様にテーブル種別がReferenceTypeにセットされています。テーブルを変更する際は該当するデータ種別に変える必要が有ります。
またSiteSettingsにはテーブル管理の情報が格納されています。情報はJson形式で格納されており、ReferenceTypeやResultIdやIssueIdの記述があります。
テーブル種別を変える場合は、それらに合わせた情報に変更する必要が有ります。

期限付きテーブルから記録テーブルに変えるスクリプト

--
-- Issue2Result
--
BEGIN
	DECLARE @ID INT = 20056;
	DECLARE @ERRCNT INT = 0;
	IF EXISTS(SELECT * FROM [Sites] WHERE [SiteId]=@ID AND [ReferenceType]='Issues')
	BEGIN
		--データ複写(Issues=>Results)
		INSERT INTO [Results](
		[SiteId],
		[ResultId],
		[Ver],
		[Title],
		[Body],
		[Status],
		[Locked],
		[Manager],[Owner],[Creator],[Updator],
		[CreatedTime],[UpdatedTime],
		[ClassA],[ClassB],[ClassC],[ClassD],[ClassE],[ClassF],[ClassG],[ClassH],[ClassI],[ClassJ],[ClassK],[ClassL],[ClassM],[ClassN],[ClassO],[ClassP],[ClassQ],[ClassR],[ClassS],[ClassT],[ClassU],[ClassV],[ClassW],[ClassX],[ClassY],[ClassZ],
		[NumA],[NumB],[NumC],[NumD],[NumE],[NumF],[NumG],[NumH],[NumI],[NumJ],[NumK],[NumL],[NumM],[NumN],[NumO],[NumP],[NumQ],[NumR],[NumS],[NumT],[NumU],[NumV],[NumW],[NumX],[NumY],[NumZ],
		[DateA],[DateB],[DateC],[DateD],[DateE],[DateF],[DateG],[DateH],[DateI],[DateJ],[DateK],[DateL],[DateM],[DateN],[DateO],[DateP],[DateQ],[DateR],[DateS],[DateT],[DateU],[DateV],[DateW],[DateX],[DateY],[DateZ],
		[DescriptionA],[DescriptionB],[DescriptionC],[DescriptionD],[DescriptionE],[DescriptionF],[DescriptionG],[DescriptionH],[DescriptionI],[DescriptionJ],[DescriptionK],[DescriptionL],[DescriptionM],[DescriptionN],[DescriptionO],[DescriptionP],[DescriptionQ],[DescriptionR],[DescriptionS],[DescriptionT],[DescriptionU],[DescriptionV],[DescriptionW],[DescriptionX],[DescriptionY],[DescriptionZ],
		[CheckA],[CheckB],[CheckC],[CheckD],[CheckE],[CheckF],[CheckG],[CheckH],[CheckI],[CheckJ],[CheckK],[CheckL],[CheckM],[CheckN],[CheckO],[CheckP],[CheckQ],[CheckR],[CheckS],[CheckT],[CheckU],[CheckV],[CheckW],[CheckX],[CheckY],[CheckZ],
		[AttachmentsA],[AttachmentsB],[AttachmentsC],[AttachmentsD],[AttachmentsE],[AttachmentsF],[AttachmentsG],[AttachmentsH],[AttachmentsI],[AttachmentsJ],[AttachmentsK],[AttachmentsL],[AttachmentsM],[AttachmentsN],[AttachmentsO],[AttachmentsP],[AttachmentsQ],[AttachmentsR],[AttachmentsS],[AttachmentsT],[AttachmentsU],[AttachmentsV],[AttachmentsW],[AttachmentsX],[AttachmentsY],[AttachmentsZ],
		[Comments]
		)
		SELECT 
		[SiteId],
		[IssueId],
		[Ver],
		ISNULL([Title],''),
		[Body],
		ISNULL([Status],''),
		[Locked],
		[Manager],[Owner],[Creator],[Updator],
		[CreatedTime],[UpdatedTime],
		[ClassA],[ClassB],[ClassC],[ClassD],[ClassE],[ClassF],[ClassG],[ClassH],[ClassI],[ClassJ],[ClassK],[ClassL],[ClassM],[ClassN],[ClassO],[ClassP],[ClassQ],[ClassR],[ClassS],[ClassT],[ClassU],[ClassV],[ClassW],[ClassX],[ClassY],[ClassZ],
		[NumA],[NumB],[NumC],[NumD],[NumE],[NumF],[NumG],[NumH],[NumI],[NumJ],[NumK],[NumL],[NumM],[NumN],[NumO],[NumP],[NumQ],[NumR],[NumS],[NumT],[NumU],[NumV],[NumW],[NumX],[NumY],[NumZ],
		[DateA],[DateB],[DateC],[DateD],[DateE],[DateF],[DateG],[DateH],[DateI],[DateJ],[DateK],[DateL],[DateM],[DateN],[DateO],[DateP],[DateQ],[DateR],[DateS],[DateT],[DateU],[DateV],[DateW],[DateX],[DateY],[DateZ],
		[DescriptionA],[DescriptionB],[DescriptionC],[DescriptionD],[DescriptionE],[DescriptionF],[DescriptionG],[DescriptionH],[DescriptionI],[DescriptionJ],[DescriptionK],[DescriptionL],[DescriptionM],[DescriptionN],[DescriptionO],[DescriptionP],[DescriptionQ],[DescriptionR],[DescriptionS],[DescriptionT],[DescriptionU],[DescriptionV],[DescriptionW],[DescriptionX],[DescriptionY],[DescriptionZ],
		[CheckA],[CheckB],[CheckC],[CheckD],[CheckE],[CheckF],[CheckG],[CheckH],[CheckI],[CheckJ],[CheckK],[CheckL],[CheckM],[CheckN],[CheckO],[CheckP],[CheckQ],[CheckR],[CheckS],[CheckT],[CheckU],[CheckV],[CheckW],[CheckX],[CheckY],[CheckZ],
		[AttachmentsA],[AttachmentsB],[AttachmentsC],[AttachmentsD],[AttachmentsE],[AttachmentsF],[AttachmentsG],[AttachmentsH],[AttachmentsI],[AttachmentsJ],[AttachmentsK],[AttachmentsL],[AttachmentsM],[AttachmentsN],[AttachmentsO],[AttachmentsP],[AttachmentsQ],[AttachmentsR],[AttachmentsS],[AttachmentsT],[AttachmentsU],[AttachmentsV],[AttachmentsW],[AttachmentsX],[AttachmentsY],[AttachmentsZ],
		[Comments]
		FROM [Issues] WHERE [SiteId]=@ID;
		IF @@ERROR<>0 SET @ERRCNT=@ERRCNT+1

		--データ複写(Issues_deleted=>Results_deleted)
		INSERT INTO [Results_deleted](
		[SiteId],
		[ResultId],
		[Ver],
		[Title],
		[Body],
		[Status],
		[Locked],
		[Manager],[Owner],[Creator],[Updator],
		[CreatedTime],[UpdatedTime],
		[ClassA],[ClassB],[ClassC],[ClassD],[ClassE],[ClassF],[ClassG],[ClassH],[ClassI],[ClassJ],[ClassK],[ClassL],[ClassM],[ClassN],[ClassO],[ClassP],[ClassQ],[ClassR],[ClassS],[ClassT],[ClassU],[ClassV],[ClassW],[ClassX],[ClassY],[ClassZ],
		[NumA],[NumB],[NumC],[NumD],[NumE],[NumF],[NumG],[NumH],[NumI],[NumJ],[NumK],[NumL],[NumM],[NumN],[NumO],[NumP],[NumQ],[NumR],[NumS],[NumT],[NumU],[NumV],[NumW],[NumX],[NumY],[NumZ],
		[DateA],[DateB],[DateC],[DateD],[DateE],[DateF],[DateG],[DateH],[DateI],[DateJ],[DateK],[DateL],[DateM],[DateN],[DateO],[DateP],[DateQ],[DateR],[DateS],[DateT],[DateU],[DateV],[DateW],[DateX],[DateY],[DateZ],
		[DescriptionA],[DescriptionB],[DescriptionC],[DescriptionD],[DescriptionE],[DescriptionF],[DescriptionG],[DescriptionH],[DescriptionI],[DescriptionJ],[DescriptionK],[DescriptionL],[DescriptionM],[DescriptionN],[DescriptionO],[DescriptionP],[DescriptionQ],[DescriptionR],[DescriptionS],[DescriptionT],[DescriptionU],[DescriptionV],[DescriptionW],[DescriptionX],[DescriptionY],[DescriptionZ],
		[CheckA],[CheckB],[CheckC],[CheckD],[CheckE],[CheckF],[CheckG],[CheckH],[CheckI],[CheckJ],[CheckK],[CheckL],[CheckM],[CheckN],[CheckO],[CheckP],[CheckQ],[CheckR],[CheckS],[CheckT],[CheckU],[CheckV],[CheckW],[CheckX],[CheckY],[CheckZ],
		[AttachmentsA],[AttachmentsB],[AttachmentsC],[AttachmentsD],[AttachmentsE],[AttachmentsF],[AttachmentsG],[AttachmentsH],[AttachmentsI],[AttachmentsJ],[AttachmentsK],[AttachmentsL],[AttachmentsM],[AttachmentsN],[AttachmentsO],[AttachmentsP],[AttachmentsQ],[AttachmentsR],[AttachmentsS],[AttachmentsT],[AttachmentsU],[AttachmentsV],[AttachmentsW],[AttachmentsX],[AttachmentsY],[AttachmentsZ],
		[Comments]
		)
		SELECT 
		[SiteId],
		[IssueId],
		[Ver],
		ISNULL([Title],''),
		[Body],
		ISNULL([Status],''),
		[Locked],
		[Manager],[Owner],[Creator],[Updator],
		[CreatedTime],[UpdatedTime],
		[ClassA],[ClassB],[ClassC],[ClassD],[ClassE],[ClassF],[ClassG],[ClassH],[ClassI],[ClassJ],[ClassK],[ClassL],[ClassM],[ClassN],[ClassO],[ClassP],[ClassQ],[ClassR],[ClassS],[ClassT],[ClassU],[ClassV],[ClassW],[ClassX],[ClassY],[ClassZ],
		[NumA],[NumB],[NumC],[NumD],[NumE],[NumF],[NumG],[NumH],[NumI],[NumJ],[NumK],[NumL],[NumM],[NumN],[NumO],[NumP],[NumQ],[NumR],[NumS],[NumT],[NumU],[NumV],[NumW],[NumX],[NumY],[NumZ],
		[DateA],[DateB],[DateC],[DateD],[DateE],[DateF],[DateG],[DateH],[DateI],[DateJ],[DateK],[DateL],[DateM],[DateN],[DateO],[DateP],[DateQ],[DateR],[DateS],[DateT],[DateU],[DateV],[DateW],[DateX],[DateY],[DateZ],
		[DescriptionA],[DescriptionB],[DescriptionC],[DescriptionD],[DescriptionE],[DescriptionF],[DescriptionG],[DescriptionH],[DescriptionI],[DescriptionJ],[DescriptionK],[DescriptionL],[DescriptionM],[DescriptionN],[DescriptionO],[DescriptionP],[DescriptionQ],[DescriptionR],[DescriptionS],[DescriptionT],[DescriptionU],[DescriptionV],[DescriptionW],[DescriptionX],[DescriptionY],[DescriptionZ],
		[CheckA],[CheckB],[CheckC],[CheckD],[CheckE],[CheckF],[CheckG],[CheckH],[CheckI],[CheckJ],[CheckK],[CheckL],[CheckM],[CheckN],[CheckO],[CheckP],[CheckQ],[CheckR],[CheckS],[CheckT],[CheckU],[CheckV],[CheckW],[CheckX],[CheckY],[CheckZ],
		[AttachmentsA],[AttachmentsB],[AttachmentsC],[AttachmentsD],[AttachmentsE],[AttachmentsF],[AttachmentsG],[AttachmentsH],[AttachmentsI],[AttachmentsJ],[AttachmentsK],[AttachmentsL],[AttachmentsM],[AttachmentsN],[AttachmentsO],[AttachmentsP],[AttachmentsQ],[AttachmentsR],[AttachmentsS],[AttachmentsT],[AttachmentsU],[AttachmentsV],[AttachmentsW],[AttachmentsX],[AttachmentsY],[AttachmentsZ],
		[Comments]
		FROM [Issues_deleted] WHERE [SiteId]=@ID;
		IF @@ERROR<>0 SET @ERRCNT=@ERRCNT+1

		--データ複写(Issues_history=>Results_history)
		INSERT INTO [Results_history](
		[SiteId],
		[ResultId],
		[Ver],
		[Title],
		[Body],
		[Status],
		[Locked],
		[Manager],[Owner],[Creator],[Updator],
		[CreatedTime],[UpdatedTime],
		[ClassA],[ClassB],[ClassC],[ClassD],[ClassE],[ClassF],[ClassG],[ClassH],[ClassI],[ClassJ],[ClassK],[ClassL],[ClassM],[ClassN],[ClassO],[ClassP],[ClassQ],[ClassR],[ClassS],[ClassT],[ClassU],[ClassV],[ClassW],[ClassX],[ClassY],[ClassZ],
		[NumA],[NumB],[NumC],[NumD],[NumE],[NumF],[NumG],[NumH],[NumI],[NumJ],[NumK],[NumL],[NumM],[NumN],[NumO],[NumP],[NumQ],[NumR],[NumS],[NumT],[NumU],[NumV],[NumW],[NumX],[NumY],[NumZ],
		[DateA],[DateB],[DateC],[DateD],[DateE],[DateF],[DateG],[DateH],[DateI],[DateJ],[DateK],[DateL],[DateM],[DateN],[DateO],[DateP],[DateQ],[DateR],[DateS],[DateT],[DateU],[DateV],[DateW],[DateX],[DateY],[DateZ],
		[DescriptionA],[DescriptionB],[DescriptionC],[DescriptionD],[DescriptionE],[DescriptionF],[DescriptionG],[DescriptionH],[DescriptionI],[DescriptionJ],[DescriptionK],[DescriptionL],[DescriptionM],[DescriptionN],[DescriptionO],[DescriptionP],[DescriptionQ],[DescriptionR],[DescriptionS],[DescriptionT],[DescriptionU],[DescriptionV],[DescriptionW],[DescriptionX],[DescriptionY],[DescriptionZ],
		[CheckA],[CheckB],[CheckC],[CheckD],[CheckE],[CheckF],[CheckG],[CheckH],[CheckI],[CheckJ],[CheckK],[CheckL],[CheckM],[CheckN],[CheckO],[CheckP],[CheckQ],[CheckR],[CheckS],[CheckT],[CheckU],[CheckV],[CheckW],[CheckX],[CheckY],[CheckZ],
		[AttachmentsA],[AttachmentsB],[AttachmentsC],[AttachmentsD],[AttachmentsE],[AttachmentsF],[AttachmentsG],[AttachmentsH],[AttachmentsI],[AttachmentsJ],[AttachmentsK],[AttachmentsL],[AttachmentsM],[AttachmentsN],[AttachmentsO],[AttachmentsP],[AttachmentsQ],[AttachmentsR],[AttachmentsS],[AttachmentsT],[AttachmentsU],[AttachmentsV],[AttachmentsW],[AttachmentsX],[AttachmentsY],[AttachmentsZ],
		[Comments]
		)
		SELECT 
		[SiteId],
		[IssueId],
		[Ver],
		[Title],
		[Body],
		[Status],
		[Locked],
		[Manager],[Owner],[Creator],[Updator],
		[CreatedTime],[UpdatedTime],
		[ClassA],[ClassB],[ClassC],[ClassD],[ClassE],[ClassF],[ClassG],[ClassH],[ClassI],[ClassJ],[ClassK],[ClassL],[ClassM],[ClassN],[ClassO],[ClassP],[ClassQ],[ClassR],[ClassS],[ClassT],[ClassU],[ClassV],[ClassW],[ClassX],[ClassY],[ClassZ],
		[NumA],[NumB],[NumC],[NumD],[NumE],[NumF],[NumG],[NumH],[NumI],[NumJ],[NumK],[NumL],[NumM],[NumN],[NumO],[NumP],[NumQ],[NumR],[NumS],[NumT],[NumU],[NumV],[NumW],[NumX],[NumY],[NumZ],
		[DateA],[DateB],[DateC],[DateD],[DateE],[DateF],[DateG],[DateH],[DateI],[DateJ],[DateK],[DateL],[DateM],[DateN],[DateO],[DateP],[DateQ],[DateR],[DateS],[DateT],[DateU],[DateV],[DateW],[DateX],[DateY],[DateZ],
		[DescriptionA],[DescriptionB],[DescriptionC],[DescriptionD],[DescriptionE],[DescriptionF],[DescriptionG],[DescriptionH],[DescriptionI],[DescriptionJ],[DescriptionK],[DescriptionL],[DescriptionM],[DescriptionN],[DescriptionO],[DescriptionP],[DescriptionQ],[DescriptionR],[DescriptionS],[DescriptionT],[DescriptionU],[DescriptionV],[DescriptionW],[DescriptionX],[DescriptionY],[DescriptionZ],
		[CheckA],[CheckB],[CheckC],[CheckD],[CheckE],[CheckF],[CheckG],[CheckH],[CheckI],[CheckJ],[CheckK],[CheckL],[CheckM],[CheckN],[CheckO],[CheckP],[CheckQ],[CheckR],[CheckS],[CheckT],[CheckU],[CheckV],[CheckW],[CheckX],[CheckY],[CheckZ],
		[AttachmentsA],[AttachmentsB],[AttachmentsC],[AttachmentsD],[AttachmentsE],[AttachmentsF],[AttachmentsG],[AttachmentsH],[AttachmentsI],[AttachmentsJ],[AttachmentsK],[AttachmentsL],[AttachmentsM],[AttachmentsN],[AttachmentsO],[AttachmentsP],[AttachmentsQ],[AttachmentsR],[AttachmentsS],[AttachmentsT],[AttachmentsU],[AttachmentsV],[AttachmentsW],[AttachmentsX],[AttachmentsY],[AttachmentsZ],
		[Comments]
		FROM [Issues_history] WHERE [SiteId]=@ID;
		IF @@ERROR<>0 SET @ERRCNT=@ERRCNT+1

		IF @ERRCNT=0
		BEGIN
			--データ削除
			DELETE FROM [Issues] WHERE [SiteId]=@ID;
			DELETE FROM [Issues_deleted] WHERE [SiteId]=@ID;
			DELETE FROM [Issues_history] WHERE [SiteId]=@ID;
			--Items情報の更新
			UPDATE [Items] SET [ReferenceType]='Results' WHERE [SiteId]=@ID AND [ReferenceType]='Issues';
			--Sites情報の更新
			DECLARE @SiteSettings VARCHAR(MAX);
			SELECT @SiteSettings = [SiteSettings] FROM [Sites] WHERE [SiteId]=@ID;
			SET @SiteSettings = REPLACE(@SiteSettings, 'Issues', 'Results');
			SET @SiteSettings = REPLACE(@SiteSettings, 'IssueId', 'ResultId');
			UPDATE [Sites] SET [ReferenceType]='Results',[SiteSettings]=@SiteSettings WHERE [SiteId]=@ID
		END
	END
END

記録テーブルから期限付きテーブルに変えるスクリプト

--
-- Result2Issue
--
--
BEGIN
	DECLARE @ID INT = 20056;
	DECLARE @ERRCNT INT = 0;
	IF EXISTS(SELECT * FROM [Sites] WHERE [SiteId]=@ID AND [ReferenceType]='Results')
	BEGIN
		--データ複写(Results=>Issues)
		INSERT INTO [Issues](
		[SiteId],
		[IssueId],
		[Ver],
		[Title],
		[Body],
		[StartTime],
		[CompletionTime],
		[WorkValue],
		[ProgressRate],
		[Status],
		[Locked],
		[Manager],[Owner],[Creator],[Updator],
		[CreatedTime],[UpdatedTime],
		[ClassA],[ClassB],[ClassC],[ClassD],[ClassE],[ClassF],[ClassG],[ClassH],[ClassI],[ClassJ],[ClassK],[ClassL],[ClassM],[ClassN],[ClassO],[ClassP],[ClassQ],[ClassR],[ClassS],[ClassT],[ClassU],[ClassV],[ClassW],[ClassX],[ClassY],[ClassZ],
		[NumA],[NumB],[NumC],[NumD],[NumE],[NumF],[NumG],[NumH],[NumI],[NumJ],[NumK],[NumL],[NumM],[NumN],[NumO],[NumP],[NumQ],[NumR],[NumS],[NumT],[NumU],[NumV],[NumW],[NumX],[NumY],[NumZ],
		[DateA],[DateB],[DateC],[DateD],[DateE],[DateF],[DateG],[DateH],[DateI],[DateJ],[DateK],[DateL],[DateM],[DateN],[DateO],[DateP],[DateQ],[DateR],[DateS],[DateT],[DateU],[DateV],[DateW],[DateX],[DateY],[DateZ],
		[DescriptionA],[DescriptionB],[DescriptionC],[DescriptionD],[DescriptionE],[DescriptionF],[DescriptionG],[DescriptionH],[DescriptionI],[DescriptionJ],[DescriptionK],[DescriptionL],[DescriptionM],[DescriptionN],[DescriptionO],[DescriptionP],[DescriptionQ],[DescriptionR],[DescriptionS],[DescriptionT],[DescriptionU],[DescriptionV],[DescriptionW],[DescriptionX],[DescriptionY],[DescriptionZ],
		[CheckA],[CheckB],[CheckC],[CheckD],[CheckE],[CheckF],[CheckG],[CheckH],[CheckI],[CheckJ],[CheckK],[CheckL],[CheckM],[CheckN],[CheckO],[CheckP],[CheckQ],[CheckR],[CheckS],[CheckT],[CheckU],[CheckV],[CheckW],[CheckX],[CheckY],[CheckZ],
		[AttachmentsA],[AttachmentsB],[AttachmentsC],[AttachmentsD],[AttachmentsE],[AttachmentsF],[AttachmentsG],[AttachmentsH],[AttachmentsI],[AttachmentsJ],[AttachmentsK],[AttachmentsL],[AttachmentsM],[AttachmentsN],[AttachmentsO],[AttachmentsP],[AttachmentsQ],[AttachmentsR],[AttachmentsS],[AttachmentsT],[AttachmentsU],[AttachmentsV],[AttachmentsW],[AttachmentsX],[AttachmentsY],[AttachmentsZ],
		[Comments]
		)
		SELECT 
		[SiteId],
		[ResultId],
		[Ver],
		ISNULL([Title],''),
		[Body],
		NULL,
		'2099/12/31 23:59:59',
		0,
		0,
		ISNULL([Status],''),
		[Locked],
		[Manager],[Owner],[Creator],[Updator],
		[CreatedTime],[UpdatedTime],
		[ClassA],[ClassB],[ClassC],[ClassD],[ClassE],[ClassF],[ClassG],[ClassH],[ClassI],[ClassJ],[ClassK],[ClassL],[ClassM],[ClassN],[ClassO],[ClassP],[ClassQ],[ClassR],[ClassS],[ClassT],[ClassU],[ClassV],[ClassW],[ClassX],[ClassY],[ClassZ],
		[NumA],[NumB],[NumC],[NumD],[NumE],[NumF],[NumG],[NumH],[NumI],[NumJ],[NumK],[NumL],[NumM],[NumN],[NumO],[NumP],[NumQ],[NumR],[NumS],[NumT],[NumU],[NumV],[NumW],[NumX],[NumY],[NumZ],
		[DateA],[DateB],[DateC],[DateD],[DateE],[DateF],[DateG],[DateH],[DateI],[DateJ],[DateK],[DateL],[DateM],[DateN],[DateO],[DateP],[DateQ],[DateR],[DateS],[DateT],[DateU],[DateV],[DateW],[DateX],[DateY],[DateZ],
		[DescriptionA],[DescriptionB],[DescriptionC],[DescriptionD],[DescriptionE],[DescriptionF],[DescriptionG],[DescriptionH],[DescriptionI],[DescriptionJ],[DescriptionK],[DescriptionL],[DescriptionM],[DescriptionN],[DescriptionO],[DescriptionP],[DescriptionQ],[DescriptionR],[DescriptionS],[DescriptionT],[DescriptionU],[DescriptionV],[DescriptionW],[DescriptionX],[DescriptionY],[DescriptionZ],
		[CheckA],[CheckB],[CheckC],[CheckD],[CheckE],[CheckF],[CheckG],[CheckH],[CheckI],[CheckJ],[CheckK],[CheckL],[CheckM],[CheckN],[CheckO],[CheckP],[CheckQ],[CheckR],[CheckS],[CheckT],[CheckU],[CheckV],[CheckW],[CheckX],[CheckY],[CheckZ],
		[AttachmentsA],[AttachmentsB],[AttachmentsC],[AttachmentsD],[AttachmentsE],[AttachmentsF],[AttachmentsG],[AttachmentsH],[AttachmentsI],[AttachmentsJ],[AttachmentsK],[AttachmentsL],[AttachmentsM],[AttachmentsN],[AttachmentsO],[AttachmentsP],[AttachmentsQ],[AttachmentsR],[AttachmentsS],[AttachmentsT],[AttachmentsU],[AttachmentsV],[AttachmentsW],[AttachmentsX],[AttachmentsY],[AttachmentsZ],
		[Comments]
		FROM [Results] WHERE [SiteId]=@ID;
		IF @@ERROR<>0 SET @ERRCNT=@ERRCNT+1

		--データ複写(Results_deleted=>Issues_deleted)
		INSERT INTO [Issues_deleted](
		[SiteId],
		[IssueId],
		[Ver],
		[Title],
		[Body],
		[StartTime],
		[CompletionTime],
		[WorkValue],
		[ProgressRate],
		[Status],
		[Locked],
		[Manager],[Owner],[Creator],[Updator],
		[CreatedTime],[UpdatedTime],
		[ClassA],[ClassB],[ClassC],[ClassD],[ClassE],[ClassF],[ClassG],[ClassH],[ClassI],[ClassJ],[ClassK],[ClassL],[ClassM],[ClassN],[ClassO],[ClassP],[ClassQ],[ClassR],[ClassS],[ClassT],[ClassU],[ClassV],[ClassW],[ClassX],[ClassY],[ClassZ],
		[NumA],[NumB],[NumC],[NumD],[NumE],[NumF],[NumG],[NumH],[NumI],[NumJ],[NumK],[NumL],[NumM],[NumN],[NumO],[NumP],[NumQ],[NumR],[NumS],[NumT],[NumU],[NumV],[NumW],[NumX],[NumY],[NumZ],
		[DateA],[DateB],[DateC],[DateD],[DateE],[DateF],[DateG],[DateH],[DateI],[DateJ],[DateK],[DateL],[DateM],[DateN],[DateO],[DateP],[DateQ],[DateR],[DateS],[DateT],[DateU],[DateV],[DateW],[DateX],[DateY],[DateZ],
		[DescriptionA],[DescriptionB],[DescriptionC],[DescriptionD],[DescriptionE],[DescriptionF],[DescriptionG],[DescriptionH],[DescriptionI],[DescriptionJ],[DescriptionK],[DescriptionL],[DescriptionM],[DescriptionN],[DescriptionO],[DescriptionP],[DescriptionQ],[DescriptionR],[DescriptionS],[DescriptionT],[DescriptionU],[DescriptionV],[DescriptionW],[DescriptionX],[DescriptionY],[DescriptionZ],
		[CheckA],[CheckB],[CheckC],[CheckD],[CheckE],[CheckF],[CheckG],[CheckH],[CheckI],[CheckJ],[CheckK],[CheckL],[CheckM],[CheckN],[CheckO],[CheckP],[CheckQ],[CheckR],[CheckS],[CheckT],[CheckU],[CheckV],[CheckW],[CheckX],[CheckY],[CheckZ],
		[AttachmentsA],[AttachmentsB],[AttachmentsC],[AttachmentsD],[AttachmentsE],[AttachmentsF],[AttachmentsG],[AttachmentsH],[AttachmentsI],[AttachmentsJ],[AttachmentsK],[AttachmentsL],[AttachmentsM],[AttachmentsN],[AttachmentsO],[AttachmentsP],[AttachmentsQ],[AttachmentsR],[AttachmentsS],[AttachmentsT],[AttachmentsU],[AttachmentsV],[AttachmentsW],[AttachmentsX],[AttachmentsY],[AttachmentsZ],
		[Comments]
		)
		SELECT 
		[SiteId],
		[ResultId],
		[Ver],
		ISNULL([Title],''),
		[Body],
		NULL,
		'2099/12/31 23:59:59',
		0,
		0,
		ISNULL([Status],''),
		[Locked],
		[Manager],[Owner],[Creator],[Updator],
		[CreatedTime],[UpdatedTime],
		[ClassA],[ClassB],[ClassC],[ClassD],[ClassE],[ClassF],[ClassG],[ClassH],[ClassI],[ClassJ],[ClassK],[ClassL],[ClassM],[ClassN],[ClassO],[ClassP],[ClassQ],[ClassR],[ClassS],[ClassT],[ClassU],[ClassV],[ClassW],[ClassX],[ClassY],[ClassZ],
		[NumA],[NumB],[NumC],[NumD],[NumE],[NumF],[NumG],[NumH],[NumI],[NumJ],[NumK],[NumL],[NumM],[NumN],[NumO],[NumP],[NumQ],[NumR],[NumS],[NumT],[NumU],[NumV],[NumW],[NumX],[NumY],[NumZ],
		[DateA],[DateB],[DateC],[DateD],[DateE],[DateF],[DateG],[DateH],[DateI],[DateJ],[DateK],[DateL],[DateM],[DateN],[DateO],[DateP],[DateQ],[DateR],[DateS],[DateT],[DateU],[DateV],[DateW],[DateX],[DateY],[DateZ],
		[DescriptionA],[DescriptionB],[DescriptionC],[DescriptionD],[DescriptionE],[DescriptionF],[DescriptionG],[DescriptionH],[DescriptionI],[DescriptionJ],[DescriptionK],[DescriptionL],[DescriptionM],[DescriptionN],[DescriptionO],[DescriptionP],[DescriptionQ],[DescriptionR],[DescriptionS],[DescriptionT],[DescriptionU],[DescriptionV],[DescriptionW],[DescriptionX],[DescriptionY],[DescriptionZ],
		[CheckA],[CheckB],[CheckC],[CheckD],[CheckE],[CheckF],[CheckG],[CheckH],[CheckI],[CheckJ],[CheckK],[CheckL],[CheckM],[CheckN],[CheckO],[CheckP],[CheckQ],[CheckR],[CheckS],[CheckT],[CheckU],[CheckV],[CheckW],[CheckX],[CheckY],[CheckZ],
		[AttachmentsA],[AttachmentsB],[AttachmentsC],[AttachmentsD],[AttachmentsE],[AttachmentsF],[AttachmentsG],[AttachmentsH],[AttachmentsI],[AttachmentsJ],[AttachmentsK],[AttachmentsL],[AttachmentsM],[AttachmentsN],[AttachmentsO],[AttachmentsP],[AttachmentsQ],[AttachmentsR],[AttachmentsS],[AttachmentsT],[AttachmentsU],[AttachmentsV],[AttachmentsW],[AttachmentsX],[AttachmentsY],[AttachmentsZ],
		[Comments]
		FROM [Results_deleted] WHERE [SiteId]=@ID;
		IF @@ERROR<>0 SET @ERRCNT=@ERRCNT+1

		--データ複写(Results_history=>Issues_history)
		INSERT INTO [Issues_history](
		[SiteId],
		[IssueId],
		[Ver],
		[Title],
		[Body],
		[StartTime],
		[CompletionTime],
		[WorkValue],
		[ProgressRate],
		[Status],
		[Locked],
		[Manager],[Owner],[Creator],[Updator],
		[CreatedTime],[UpdatedTime],
		[ClassA],[ClassB],[ClassC],[ClassD],[ClassE],[ClassF],[ClassG],[ClassH],[ClassI],[ClassJ],[ClassK],[ClassL],[ClassM],[ClassN],[ClassO],[ClassP],[ClassQ],[ClassR],[ClassS],[ClassT],[ClassU],[ClassV],[ClassW],[ClassX],[ClassY],[ClassZ],
		[NumA],[NumB],[NumC],[NumD],[NumE],[NumF],[NumG],[NumH],[NumI],[NumJ],[NumK],[NumL],[NumM],[NumN],[NumO],[NumP],[NumQ],[NumR],[NumS],[NumT],[NumU],[NumV],[NumW],[NumX],[NumY],[NumZ],
		[DateA],[DateB],[DateC],[DateD],[DateE],[DateF],[DateG],[DateH],[DateI],[DateJ],[DateK],[DateL],[DateM],[DateN],[DateO],[DateP],[DateQ],[DateR],[DateS],[DateT],[DateU],[DateV],[DateW],[DateX],[DateY],[DateZ],
		[DescriptionA],[DescriptionB],[DescriptionC],[DescriptionD],[DescriptionE],[DescriptionF],[DescriptionG],[DescriptionH],[DescriptionI],[DescriptionJ],[DescriptionK],[DescriptionL],[DescriptionM],[DescriptionN],[DescriptionO],[DescriptionP],[DescriptionQ],[DescriptionR],[DescriptionS],[DescriptionT],[DescriptionU],[DescriptionV],[DescriptionW],[DescriptionX],[DescriptionY],[DescriptionZ],
		[CheckA],[CheckB],[CheckC],[CheckD],[CheckE],[CheckF],[CheckG],[CheckH],[CheckI],[CheckJ],[CheckK],[CheckL],[CheckM],[CheckN],[CheckO],[CheckP],[CheckQ],[CheckR],[CheckS],[CheckT],[CheckU],[CheckV],[CheckW],[CheckX],[CheckY],[CheckZ],
		[AttachmentsA],[AttachmentsB],[AttachmentsC],[AttachmentsD],[AttachmentsE],[AttachmentsF],[AttachmentsG],[AttachmentsH],[AttachmentsI],[AttachmentsJ],[AttachmentsK],[AttachmentsL],[AttachmentsM],[AttachmentsN],[AttachmentsO],[AttachmentsP],[AttachmentsQ],[AttachmentsR],[AttachmentsS],[AttachmentsT],[AttachmentsU],[AttachmentsV],[AttachmentsW],[AttachmentsX],[AttachmentsY],[AttachmentsZ],
		[Comments]
		)
		SELECT 
		[SiteId],
		[ResultId],
		[Ver],
		ISNULL([Title],''),
		[Body],
		NULL,
		'2099/12/31 23:59:59',
		0,
		0,
		ISNULL([Status],''),
		[Locked],
		[Manager],[Owner],[Creator],[Updator],
		[CreatedTime],[UpdatedTime],
		[ClassA],[ClassB],[ClassC],[ClassD],[ClassE],[ClassF],[ClassG],[ClassH],[ClassI],[ClassJ],[ClassK],[ClassL],[ClassM],[ClassN],[ClassO],[ClassP],[ClassQ],[ClassR],[ClassS],[ClassT],[ClassU],[ClassV],[ClassW],[ClassX],[ClassY],[ClassZ],
		[NumA],[NumB],[NumC],[NumD],[NumE],[NumF],[NumG],[NumH],[NumI],[NumJ],[NumK],[NumL],[NumM],[NumN],[NumO],[NumP],[NumQ],[NumR],[NumS],[NumT],[NumU],[NumV],[NumW],[NumX],[NumY],[NumZ],
		[DateA],[DateB],[DateC],[DateD],[DateE],[DateF],[DateG],[DateH],[DateI],[DateJ],[DateK],[DateL],[DateM],[DateN],[DateO],[DateP],[DateQ],[DateR],[DateS],[DateT],[DateU],[DateV],[DateW],[DateX],[DateY],[DateZ],
		[DescriptionA],[DescriptionB],[DescriptionC],[DescriptionD],[DescriptionE],[DescriptionF],[DescriptionG],[DescriptionH],[DescriptionI],[DescriptionJ],[DescriptionK],[DescriptionL],[DescriptionM],[DescriptionN],[DescriptionO],[DescriptionP],[DescriptionQ],[DescriptionR],[DescriptionS],[DescriptionT],[DescriptionU],[DescriptionV],[DescriptionW],[DescriptionX],[DescriptionY],[DescriptionZ],
		[CheckA],[CheckB],[CheckC],[CheckD],[CheckE],[CheckF],[CheckG],[CheckH],[CheckI],[CheckJ],[CheckK],[CheckL],[CheckM],[CheckN],[CheckO],[CheckP],[CheckQ],[CheckR],[CheckS],[CheckT],[CheckU],[CheckV],[CheckW],[CheckX],[CheckY],[CheckZ],
		[AttachmentsA],[AttachmentsB],[AttachmentsC],[AttachmentsD],[AttachmentsE],[AttachmentsF],[AttachmentsG],[AttachmentsH],[AttachmentsI],[AttachmentsJ],[AttachmentsK],[AttachmentsL],[AttachmentsM],[AttachmentsN],[AttachmentsO],[AttachmentsP],[AttachmentsQ],[AttachmentsR],[AttachmentsS],[AttachmentsT],[AttachmentsU],[AttachmentsV],[AttachmentsW],[AttachmentsX],[AttachmentsY],[AttachmentsZ],
		[Comments]
		FROM [Results_history] WHERE [SiteId]=@ID;
		IF @@ERROR<>0 SET @ERRCNT=@ERRCNT+1

		IF @ERRCNT=0
		BEGIN
			--データ削除
			DELETE FROM [Results] WHERE [SiteId]=@ID;
			DELETE FROM [Results_deleted] WHERE [SiteId]=@ID;
			DELETE FROM [Results_history] WHERE [SiteId]=@ID;
			--Items情報の更新
			UPDATE [Items] SET [ReferenceType]='Issues' WHERE [SiteId]=@ID AND [ReferenceType]='Results';
			--Sites情報の更新
			DECLARE @SiteSettings VARCHAR(MAX);
			SELECT @SiteSettings = [SiteSettings] FROM [Sites] WHERE [SiteId]=@ID;
			SET @SiteSettings = REPLACE(@SiteSettings, 'Results', 'Issues');
			SET @SiteSettings = REPLACE(@SiteSettings, 'ResultId', 'IssueId');
			UPDATE [Sites] SET [ReferenceType]='Issues',[SiteSettings]=@SiteSettings WHERE [SiteId]=@ID
		END
	END
END

懸念点

IssuesからResultsに変更した場合、Issuesにのみ存在するフィールドがSiteSettingsに残ってしまいます。
動作検証した場合には問題有りませんでしたが不要要素により動作に支障をきたす場合が有ります。

最後に

本内容は私的調査による情報になります。本内容の実行により問題が発生しても保証しかねるのでご了承ください。

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?