同一データベース内で旧テーブルを新テーブルに移行する

環境https://gyazo.com/f443bb729a11be2379910ad331f25d6f

SQL Server 14.0.1000.169

やりたいこと

現行のUSERSテーブルがある

ちょっとわかりにくい…

これを新しい列定義の新USERSテーブルにしたい

件数は1000件くらい

現行のUSERSテーブルのテーブル構成例

code:mmd

erDiagram

USERS {

int user_id PK "社員番号"

nvarchar(8) user_name "社員名"

nvarchar(8) password "パスワード"

int kengen "権限1(1:一般, 2:役職, 9:開発用)"

int kengen2 "権限2(1:xx部門, 2:yy部門, 3:zz部門, 9:開発用)"

datetime2 created_at "登録日"

datetime2 updated_at "更新日"

}

注意点

passwordは8文字…

kengenはコードを見ると決済できるかの権限みたい

nullありで、nullだと過去所属していた人らしい

kengen2はコードを見ると画面表示制御用の権限みたい

新しいUSERSテーブルのテーブル構成例

code:mmd

erDiagram

USERS {

int user_id PK "社員番号"

nvarchar(8) user_name "社員名"

nvarchar(256) password "パスワード(ハッシュ値で格納する)"

int view_permission "閲覧権限(1:xx部門, 2:yy部門, 3:zz部門)"

boolean is_manager "役職者か一般社員か"

boolean is_developer "開発者かどうか"

boolean is_currently_affiliate "今所属しているユーザーかどうか"

datetime2 created_at "登録日"

datetime2 updated_at "更新日"

}

変更点

passwordの格納方法を変更するため、文字列長を拡張

kengenとkengen2の整理

各列の値が9なら開発者と判断していたのをis_developer列で判断するようにした

今所属しているかどうかはnullじゃなくてis_currently_affiliate列で判断するようにした

役職かどうかを判断しているkengen列はis_managerに名前を変えた

閲覧権限を判断しているkengen2列はview_permissionに名前を変えた

diffだとこう

code:diff

int user_id PK "社員番号"

nvarchar(8) user_name "社員名"

- nvarchar(8) password "パスワード"

- int kengen "権限1(1:一般, 2:役職, 9:開発用)"

- int kengen2 "権限2(1:xx部門, 2:yy部門, 3:zz部門, 9:開発用)"

+ nvarchar(256) password "パスワード(ハッシュ値で格納する)"

+ int view_permission "閲覧権限(1:xx部門, 2:yy部門, 3:zz部門)"

+ boolean is_manager "役職者か一般社員か"

+ boolean is_developer "開発者かどうか"

+ boolean is_currently_affiliate "今所属しているユーザーかどうか"

datetime2 created_at "登録日"

datetime2 updated_at "更新日"

列を移行するならこんな感じ？

code:mmd

flowchart LR

subgraph "USERS(現行仕様)"

o_id"user_id"

o_name"user_name"

o_pass"password"

o_ken"kengen"

o_ken2"kengen2"

o_cre"created_at"

o_upd"updated_at"

end

subgraph "TMP_USERS(名称変更)"

t_id"user_id"

t_name"user_name"

t_pass"password"

t_ken"kengen"

t_ken2"kengen2"

t_cre"created_at"

t_upd"updated_at"

end

subgraph "USERS(新仕様で作り直し)"

n_id"user_id"

n_name"user_name"

n_pass"password"

n_view"view_permission"

n_manager"is_manager"

n_develop"is_developer"

n_affiliate"is_currently_affiliate"

n_cre"created_at"

n_upd"updated_at"

end

o_id --> t_id

o_name --> t_name

o_pass --> t_pass

o_ken --> t_ken

o_ken2 --> t_ken2

o_cre --> t_cre

o_upd --> t_upd

t_id -- ストレートに移行 --> n_id

t_name -- ストレートに移行 --> n_name

t_pass -- 列長を256に変更 --> n_pass

t_ken -- 役職者ならtrue, 一般社員と開発者はfalse --> n_manager

t_ken -- 開発者ならtrue, それ以外はfalse --> n_develop

t_ken -- 現在所属ならtrue, 過去所属(null)はfalse --> n_affiliate

t_ken2 -- 開発者は所属部門で登録 --> n_view

t_cre -- ストレートに移行 --> n_cre

t_upd -- 移行日時に統一 --> n_upd