期限付きテーブルと記録テーブルの相互変換
作成済みのサイトを「期限付きテーブル」から「記録テーブル」へ（またはその逆へ）変換する手順です。項目の移動機能ではサイトの種類をまたいだ移動はできず、作り直すとエクスポート・インポートの手間がかかります。データベースを直接書き換えることで、データを保ったまま変換できます。
INFO
SQL Server・PostgreSQL・MySQL のタブから使用中の DBMS を選んでください。@SiteId は対象サイトの ID に置き換えるか、実行ツールでパラメータとして設定します。PostgreSQL の SQL コンソールでは @SiteId をそのまま実行できません。
DANGER
データベースを直接書き換える操作です。作業前に必ずデータベースのバックアップを取得してください。
作業の流れ
- 準備:
SiteIdの取得、バックアップ、項目のマッピング決定、リンクしているテーブルの洗い出し SitesテーブルのReferenceTypeとSiteSettingsを書き換えるItemsテーブルのReferenceTypeを書き換える- データを INSERT-SELECT で詰め替える
- リンクを作り直す
- 動作確認
- 変換元テーブルのデータを削除する（運用が安定してからでよい）
準備
SiteId の取得とバックアップ
変換したいサイトの SiteId を控え、データベースのバックアップを取得します。以降の SQL では @SiteId として使います。
項目のマッピングを決める
期限付きテーブルと記録テーブルには、互いに存在しない項目や NULL 許容の違いがあります。
| 項目 | 型 | 期限付きテーブル | 記録テーブル |
|---|---|---|---|
| ReferenceId | long | IssueId | ResultId |
| StartTime | datetime | ○ | × |
| CompletionTime | datetime | ○（NULL 非許容） | × |
| WorkValue | decimal | ○ | × |
| ProgressRate | decimal | ○ | × |
| Title | nvarchar | NULL 非許容 | NULL 許容 |
| Status | int | NULL 非許容 | NULL 許容 |
- 期限付きテーブル → 記録テーブル: 期限付きテーブルにしかない項目を、記録テーブルの適当な項目にマップします。
- 記録テーブル → 期限付きテーブル: NULL 許容または存在しない項目から NULL 非許容の項目へ移すことになるため、何らかのデフォルト値を与える必要があります。
リンクしているテーブルを探す
対象テーブルにリンクを設定しているテーブルが JSON 形式の設定（View や Lookup）で固有項目を使っている場合は、再設定が必要です。対象テーブルにリンクしているテーブルは次の SQL で取得できます。結果をメモしておいてください。
sql
SELECT
[SourceId] AS [SiteId]
FROM
[Links]
WHERE
[DestinationId] = @SiteIdsql
SELECT
"SourceId" AS "SiteId"
FROM
"Links"
WHERE
"DestinationId" = @SiteIdsql
SELECT
`SourceId` AS `SiteId`
FROM
`Links`
WHERE
`DestinationId` = @SiteId変換作業
Sites テーブルの ReferenceType を書き換える
記録テーブルから期限付きテーブルへ
sql
UPDATE
[Sites]
SET
[ReferenceType] = 'Issues'
WHERE
[SiteId] = @SiteId
AND [ReferenceType] = 'Results'sql
UPDATE
"Sites"
SET
"ReferenceType" = 'Issues'
WHERE
"SiteId" = @SiteId
AND "ReferenceType" = 'Results'sql
UPDATE
`Sites`
SET
`ReferenceType` = 'Issues'
WHERE
`SiteId` = @SiteId
AND `ReferenceType` = 'Results'期限付きテーブルから記録テーブルへ
sql
UPDATE
[Sites]
SET
[ReferenceType] = 'Results'
WHERE
[SiteId] = @SiteId
AND [ReferenceType] = 'Issues'sql
UPDATE
"Sites"
SET
"ReferenceType" = 'Results'
WHERE
"SiteId" = @SiteId
AND "ReferenceType" = 'Issues'sql
UPDATE
`Sites`
SET
`ReferenceType` = 'Results'
WHERE
`SiteId` = @SiteId
AND `ReferenceType` = 'Issues'Sites テーブルの SiteSettings を書き換える
SiteSettings 列に格納されているサイト設定の JSON を、決めたマッピングに従って書き換えます。インデントのない状態で格納されているので、VS Code などでフォーマットしてから編集すると楽です。書き換えたら SiteSettings 列に書き戻します。
記録テーブルから期限付きテーブルへ変換し、DateA を CompletionTime に移す場合の例（抜粋）です。
diff
{
"Version": 1.017,
- "ReferenceType": "Results",
+ "ReferenceType": "Issues",
"GridColumns": [
- "ResultId",
+ "IssueId",
"Status",
...
],
"EditorColumnHash": {
"General": [
"Status",
- "DateA",
+ "CompletionTime",
"Title",
...
]
},
"Columns": [
{
- "ColumnName": "DateA",
+ "ColumnName": "CompletionTime",
"LabelText": "対応日"
},
...
],
"ServerScripts": [
{
"Title": "完了日に日付に今日をセット",
"BeforeCreate": true,
"BeforeUpdate": true,
- "Body": "if (model.DateA.getTime() == (new Date(1899,11,30)).getTime() && model.Status === 900) {\n model.DateA = utilities.Today();\n}",
+ "Body": "if (model.CompletionTime.getTime() == (new Date(1899,11,30)).getTime() && model.Status === 900) {\n model.CompletionTime = utilities.Today();\n}",
"Id": 1
}
]
}GridColumns・EditorColumnHash・LinkColumns・HistoryColumns・Columns のほか、スクリプトやサーバースクリプトの中で参照している項目名も書き換えが必要 です。
Items テーブルの ReferenceType を書き換える
記録テーブルから期限付きテーブルへ
sql
UPDATE
[Items]
SET
[ReferenceType] = 'Issues'
WHERE
[SiteId] = @SiteId
AND [ReferenceType] = 'Results'sql
UPDATE
"Items"
SET
"ReferenceType" = 'Issues'
WHERE
"SiteId" = @SiteId
AND "ReferenceType" = 'Results'sql
UPDATE
`Items`
SET
`ReferenceType` = 'Issues'
WHERE
`SiteId` = @SiteId
AND `ReferenceType` = 'Results'期限付きテーブルから記録テーブルへ
sql
UPDATE
[Items]
SET
[ReferenceType] = 'Results'
WHERE
[SiteId] = @SiteId
AND [ReferenceType] = 'Issues'sql
UPDATE
"Items"
SET
"ReferenceType" = 'Results'
WHERE
"SiteId" = @SiteId
AND "ReferenceType" = 'Issues'sql
UPDATE
`Items`
SET
`ReferenceType` = 'Results'
WHERE
`SiteId` = @SiteId
AND `ReferenceType` = 'Issues'データを詰め替える
INSERT-SELECT でデータを移します。同名の項目はそのまま、互いに存在しない項目はマッピングに従って指定します。記録テーブルから期限付きテーブルへ移す例（列の一覧は抜粋）です。
sql
INSERT INTO
[dbo].[Issues] (
[SiteId],
[IssueId],
[Ver],
[Title],
[Body],
[StartTime],
[CompletionTime],
[Status],
[Manager],
[Owner],
[Locked],
[ClassA],
-- ... ClassB 〜 ClassZ、NumA 〜 NumZ、DateA 〜 DateZ、
-- DescriptionA 〜 DescriptionZ、CheckA 〜 CheckZ、AttachmentsA 〜 AttachmentsZ
[Comments],
[Creator],
[Updator],
[CreatedTime]
)
SELECT
[SiteId],
[ResultId] AS [IssueId],
[Ver],
ISNULL([Title], '') AS [Title],
[Body],
[DateA] AS [StartTime],
ISNULL([DateA], '1899-12-30') AS [CompletionTime],
ISNULL([Status], 0) AS [Status],
[Manager],
[Owner],
[Locked],
[ClassA],
-- ... INSERT 側と同じ列を同じ順で列挙
[Comments],
[Creator],
[Updator],
[CreatedTime]
FROM
[dbo].[Results]
WHERE
[SiteId] = @SiteId;sql
INSERT INTO
"Issues" (
"SiteId",
"IssueId",
"Ver",
"Title",
"Body",
"StartTime",
"CompletionTime",
"Status",
"Manager",
"Owner",
"Locked",
"ClassA",
-- ... ClassB 〜 ClassZ、NumA 〜 NumZ、DateA 〜 DateZ、
-- DescriptionA 〜 DescriptionZ、CheckA 〜 CheckZ、AttachmentsA 〜 AttachmentsZ
"Comments",
"Creator",
"Updator",
"CreatedTime"
)
SELECT
"SiteId",
"ResultId" AS "IssueId",
"Ver",
COALESCE("Title", '') AS "Title",
"Body",
"DateA" AS "StartTime",
COALESCE("DateA", '1899-12-30') AS "CompletionTime",
COALESCE("Status", 0) AS "Status",
"Manager",
"Owner",
"Locked",
"ClassA",
-- ... INSERT 側と同じ列を同じ順で列挙
"Comments",
"Creator",
"Updator",
"CreatedTime"
FROM
"Results"
WHERE
"SiteId" = @SiteId;sql
INSERT INTO
`Issues` (
`SiteId`,
`IssueId`,
`Ver`,
`Title`,
`Body`,
`StartTime`,
`CompletionTime`,
`Status`,
`Manager`,
`Owner`,
`Locked`,
`ClassA`,
-- ... ClassB 〜 ClassZ、NumA 〜 NumZ、DateA 〜 DateZ、
-- DescriptionA 〜 DescriptionZ、CheckA 〜 CheckZ、AttachmentsA 〜 AttachmentsZ
`Comments`,
`Creator`,
`Updator`,
`CreatedTime`
)
SELECT
`SiteId`,
`ResultId` AS `IssueId`,
`Ver`,
COALESCE(`Title`, '') AS `Title`,
`Body`,
`DateA` AS `StartTime`,
COALESCE(`DateA`, '1899-12-30') AS `CompletionTime`,
COALESCE(`Status`, 0) AS `Status`,
`Manager`,
`Owner`,
`Locked`,
`ClassA`,
-- ... INSERT 側と同じ列を同じ順で列挙
`Comments`,
`Creator`,
`Updator`,
`CreatedTime`
FROM
`Results`
WHERE
`SiteId` = @SiteId;TitleとStatusは NULL 許容から NULL 非許容の項目へ移すため、SQL Server はISNULL、PostgreSQL・MySQL はCOALESCEで空文字・0にしています。StartTimeとCompletionTimeにはDateAをマップしています。DateAは NULL 許容（Date.json）でCompletionTimeは NULL 非許容のため、未入力を表す1899-12-30を補っています。- 1.5.8.1 のカラム定義では、
Statusは int 型で、期限付きテーブルでは NULL 非許容、記録テーブルでは NULL 許容です（Issues_Status.json、Results_Status.json）。 - 全列を列挙した SQL は下の「全列を列挙した SQL」を開いてください。
全列を列挙した SQL（記録テーブルから期限付きテーブルへ）
sql
INSERT INTO
[dbo].[Issues] (
[SiteId],
[IssueId],
[Ver],
[Title],
[Body],
[StartTime],
[CompletionTime],
[Status],
[Manager],
[Owner],
[Locked],
[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],
[Creator],
[Updator],
[CreatedTime]
)
SELECT
[SiteId],
[ResultId] AS [IssueId],
[Ver],
ISNULL([Title], '') AS [Title],
[Body],
[DateA] AS [StartTime],
ISNULL([DateA], '1899-12-30') AS [CompletionTime],
ISNULL([Status], 0) AS [Status],
[Manager],
[Owner],
[Locked],
[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],
[Creator],
[Updator],
[CreatedTime]
FROM
[dbo].[Results]
WHERE
[SiteId] = @SiteId;sql
INSERT INTO
"Issues" (
"SiteId",
"IssueId",
"Ver",
"Title",
"Body",
"StartTime",
"CompletionTime",
"Status",
"Manager",
"Owner",
"Locked",
"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",
"Creator",
"Updator",
"CreatedTime"
)
SELECT
"SiteId",
"ResultId" AS "IssueId",
"Ver",
COALESCE("Title", '') AS "Title",
"Body",
"DateA" AS "StartTime",
COALESCE("DateA", '1899-12-30') AS "CompletionTime",
COALESCE("Status", 0) AS "Status",
"Manager",
"Owner",
"Locked",
"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",
"Creator",
"Updator",
"CreatedTime"
FROM
"Results"
WHERE
"SiteId" = @SiteId;sql
INSERT INTO
`Issues` (
`SiteId`,
`IssueId`,
`Ver`,
`Title`,
`Body`,
`StartTime`,
`CompletionTime`,
`Status`,
`Manager`,
`Owner`,
`Locked`,
`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`,
`Creator`,
`Updator`,
`CreatedTime`
)
SELECT
`SiteId`,
`ResultId` AS `IssueId`,
`Ver`,
COALESCE(`Title`, '') AS `Title`,
`Body`,
`DateA` AS `StartTime`,
COALESCE(`DateA`, '1899-12-30') AS `CompletionTime`,
COALESCE(`Status`, 0) AS `Status`,
`Manager`,
`Owner`,
`Locked`,
`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`,
`Creator`,
`Updator`,
`CreatedTime`
FROM
`Results`
WHERE
`SiteId` = @SiteId;:::
INFO
SELECT に対象サイトを絞る条件がないと全サイトの記録が移されてしまうため、WHERE [SiteId] = @SiteId を加えています。
履歴を引き継ぐ場合は Results_history / Issues_history テーブル、ゴミ箱を引き継ぐ場合は Results_deleted / Issues_deleted テーブルにも同様の処理を行います。
リンクを作り直す
準備でメモしたリンク元のテーブルについて、リンクを再設定します。設定が必要になるのは JSON 形式で固有項目を指定している場合だけですが、念のためすべてのテーブルを確認してください。
動作確認
実際に操作して、問題なく動作することを確認します。
変換元のデータを削除する
INSERT-SELECT で詰め替えたため、変換元のテーブルにはデータが残っています。急ぐ必要はないので、運用が安定したタイミングで削除します（記録テーブルから期限付きテーブルへ変換した場合の例）。
sql
DELETE FROM [Results] WHERE [SiteId] = @SiteId;
DELETE FROM [Results_deleted] WHERE [SiteId] = @SiteId;
DELETE FROM [Results_history] WHERE [SiteId] = @SiteId;sql
DELETE FROM "Results" WHERE "SiteId" = @SiteId;
DELETE FROM "Results_deleted" WHERE "SiteId" = @SiteId;
DELETE FROM "Results_history" WHERE "SiteId" = @SiteId;sql
DELETE FROM `Results` WHERE `SiteId` = @SiteId;
DELETE FROM `Results_deleted` WHERE `SiteId` = @SiteId;
DELETE FROM `Results_history` WHERE `SiteId` = @SiteId;