インデックスや制約を含んだテーブルの複製方法について

Question

お世話になります。

インデックスや制約を含めた上で、テーブルの複製を行う方法はありますでしょうか？
以下のクエリを実行すると、テーブルの複製は行えるのですが、インデックスや制約は含まれてきません。

select * into 複製先部門テーブル名
from 部門テーブル名

お手数ですが、よろしくお願いいたします。

Answer 1

こんにちは、フォーラムオペレータ大久保です。

手段はクエリー実行でないとだめでしょうか？また、繰り返し同じ動作を実行する必要はありますか？
複写先は同じデータベースですか？


複製先部門テーブル名 というテーブル名称を挙げられていますので、新しい部門ができたときなどに既存の部門のデータをコピーする といったようなイメージの処理を実行されてるのかな？と思いましたが当たってますか？
だとしますと、同じデータベース内への複写で、プログラム（ストアドプロシージャなど）から処理を実行できないとだめですよね。

ご利用のSQL Serverのバージョンがわからないんですが、Management Studio でテーブル一覧を表示し、該当のテーブルの上で右クリックし「名前を付けてテーブルをスクリプト化」->「CREATE」 を実行しますと、そのテーブルを作成するスクリプトを作ってくれます。
このスクリプトには、Primari Key 制約やその他の制約を作る情報もしっかり入ってますので、これを実行すれば同じ条件のテーブルが作成できます。


もし、「プログラムの中からじゃなくて、単に一回だけデータを移行したいだけなんだけど」という話でしたら、Management Studio でテーブルをコピーしてしまうのが簡単だと思います。

それではー

# もっといい方法がある！とおっしゃる方からのツッコミ、お待ちしてます。

---

マイクロソフト株式会社 フォーラム オペレータ 大久保 直美

Answer 2

ありがとうございます。

用途としては、開発中やテストなどで一時的にテーブルの中身を空にしたいけど、あとから戻したい場合を想定しています。イメージとしては、まずテーブルを複製しておいて、中身を空にします。挙動の確認が済んだら、空になっているテーブルは削除してしまい、複製しておいたテーブルをリネームしてもとに戻します。
select * into 複製先部門テーブル名　from 部門テーブル名という記述で、テーブルの列やデータはコピーできるので、オプションで何かを付加すれば、制約などもコピーしてくれるのかな？と探しているのですが、どうも見つからなかったので質問させていただいた次第です。なので、クエリでそんなことができないか探しています。

利用しているSQL Serverのバージョンは2005と2008です。2008でしかできない方法でもかまいません。
記載していただいた「名前を付けてテーブルをスクリプト化」ですと、スキーマの定義スクリプトは生成されるのですが、データについては作成されてこないかと思います。
ただ、これに関していえば、データベースの右クリック-タスク-スクリプトの生成のウィザードの中で、データのスクリプトの生成オプションを付けてあげれば、データの部分もついてくるので、
スクリプトとしては生成できます。ですが、ちょっと毎回このウィザードを動かすのもなかなか手順が必要なので、ささっとクエリでかける複製方法がないかどうかを探してます。。。そんなのないと言われたらおしまいですね。。。

あと、すみません、よろしければ｢Management Studio でテーブルをコピー｣というのがどういう操作なのか教えていただけないでしょうか？
簡単にテーブルを丸ごとコピーできる方法があるのでしょうか？

Answer 3

用途としては、開発中やテストなどで一時的にテーブルの中身を空にしたいけど、あとから戻したい場合を想定しています。

そのような用途であれば、SQL Server Management Studioで当該データベースを右クリックし、「タスク」でバックアップおよび復元を行えばいいような気がします。データベース単位のバックアップ、復元になりますが、さほど時間はかからないと思います。
しかし本来は、テスト用のデータベースを用意すべきだと思います。同一マシンでも複数のSQL Serverのインスタンスをインストールできます。

---

★良い回答には回答済みマークを付けよう！ わんくま同盟 MVP - Visual C# http://blogs.wankuma.com/trapemiya/

Answer 4

こんにちは、フォーラムオペレータ大久保です。


テーブル生成のスクリプトは一度作ればずっと使えると思ったんですが、もしかしてテーブルのスキーマ定義自体が変わる可能性があるんでしょうか？
だとするとその都度スクリプトを作らないとだめですね。

あと、データのバックアップをしたいということであれば、制約までコピーする必要ないんじゃないでしょうか？
データだけ退避しておいて元に戻すのなら select ～ into でもいいような気がしました。


trapemiya さんがご紹介くださった、Management Studio の「タスク」では、データベースのコピーだけでなくテーブル単位のコピーもできますよ。（trapemiya さん、ありがとうございます。）
その場でささっと実行するだけであれば、この方法が一番簡単です。ウィザード方式で実行できますので、まずはお試しください。

---

マイクロソフト株式会社 フォーラム オペレータ 大久保 直美

Answer 5

こんにちは、naginoです。

個人的には、trapemiya 様ご提案のバックアップ＆復元がお勧めです。
最も確実かつ Edition に依存しない標準機能のため、間違いがありません。

色々手段はあるのですが、それぞれ特徴、制限があり、それらを全てまとめるだけでコラムになるぐらいの話題になってしまいます。
一応走り書きですが、ぱっと思い出せる範囲内で以下記述しておきます。
# 記憶違いがあったら申し訳ありません。
# 不足、誤り等について識者の方々のご指摘等歓迎いたします。

------------------------------------------------------

●バックアップ＆復元
データベースに格納されている全情報を保存できるため、多くの場合において有効。
システムデータベースに保存される情報（ログイン等）については別途対応が必要。
データベース単位の保存になるため、特定テーブルのみは不可能。
データベースのサイズが大きい場合は処理時間がかかる。
ただし 2008 Enterprise Edition からは標準で圧縮バックアップが可能。
# ファイル単位での復元などもありますが、割愛させてください。

●デタッチ＆アタッチ
デタッチ後にファイルを複製し、アタッチする。
データベースに格納されている全情報を保存できるため、多くの場合において有効。
システムデータベースに保存される情報（ログイン等）については別途対応が必要。
データベース単位の保存になるため、特定テーブルのみは不可能。
バックアップ＆復元と異なり、デタッチ～アタッチの間はデータベースが使用できない。
ファイル操作のため、直感的な管理が行いやすい。

●データベーススナップショット
データベースのほとんどの情報を保存できるため、多くの場合において有効。
（FILESTREAMは保存できない）
システムデータベースに保存される情報（ログイン等）については別途対応が必要。
データベース単位の保存になるため、特定テーブルのみは不可能。
バックアップ＆復元より高速なことが多い。
2005 以降の Enterprise Edition、 Developer Edition のみ可能。
http://msdn.microsoft.com/ja-jp/library/ms189940.aspx

●SELECT ～ INTO ～ / BCP / インポートエクスポートウィザード
データのみ保存可能。
インデックスや制約は保存できない。
テーブル、ビューなどオブジェクト単位で選択可能。
データを別テーブルにコピーしておき、作業後に元テーブルの中身を入れ替えることで元に戻すことが可能。
元に戻す際に DML トリガについて注意が必要。
また、IDENTITY などによる連番などについても注意が必要。
制限は多いが、手軽。

●サードパーティー製バックアップツール
テーブル単位のバックアップ＆復元に対応しているツールが存在します。
ツールによって機能や価格がまちまちですので、割愛させてください。

●スクリプト化（SQL Server Management Studio のオブジェクトエクスプローラでテーブルを右クリック）
スキーマのみ保存可能。
テーブル、ビューなどオブジェクト単位で選択可能。

●スクリプト化（SQL Server Management Studio のオブジェクトエクスプローラでデータベースを右クリック＞タスク＞スクリプトの生成）
スキーマとデータをあわせて保存可能。
作業後はテーブルを削除して、作成したスクリプトからスキーマとデータを CREATE & INSERT する。
テーブル、ビューなどオブジェクト単位で選択可能。

●SSIS
プログラミングが可能なため、最も自由度が高いが、作成するコストも高いので割愛させてください。

------------------------------------------------------

あとは PowerShell を使うですとか、SQL Server Database Publishing Wizard を使うですとか、他にも技がありそうなのですが、概ねメジャーどころは上記になるかと思います。
# それにしても、振り返ると結構色々ありますね。

ご参考になれば幸いです。

---

MCITP（Database Developer/Database Administrator）

Answer 6

皆さんいろいろな回答ありがとうございました。参考に作業を進めてゆきたいと思います。