テーブルの列の追加について

Question

失礼します。

テーブルの列の追加について質問です。

【やりたいこと】

①列を途中に挿入する。

②既存のレコードデータを復旧させる。

【内容】

①列あ、列い,列うがある（すべてverchar(1)とします）

②レコードが以下のようにある

a b c

d e f

③「列あ」と「列い」の間に、「列わ」(int型）を挿入する。初期値は0でよい

④挿入後、レコードを以下のようにする

a 0 b c

d 0 e f

【やってみたこと】

①bcpコマンドで、「データ」と「フォーマット(XML)」を出力

②テーブルを削除。CREATEで、「列わ」を含めてテーブルを最初から作成。

③bcpコマンドで、-f　でフォーマットを指定し、データを戻す。

【できなかったこと】

上記③で、bcpを実行したところ、SQL State= S1000 NativeError=0

ファイル内で予期しないEOFが検出されました

とでてインポートできない。

【考察】

ＸＭＬのフォーマットファイルは特に編集していません。

この部分と、インポートするデータファイルの紐づけがうまくいっていないと推測します。

他の方法も含めて、ご教示ください。

Answer 1

SQL Server のことを質問するなら SQL Server のバージョンぐらいは最初に書いていただけませんか。

以下のページが参考になりませんか？　（未検証です）

http://treeapps.hatenablog.com/entry/2013/10/19/003323

Answer 2

＞他の方法も含めて、ご教示ください。

SQL Server Management Studioで、③「列あ」と「列い」の間に、「列わ」(int型）を挿入する。
その後、以下のSQLを実行する。

update テーブル名 set 列わ = 0

---

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

Answer 3

大変申し訳ございません。

SQL Server 2012 ExpressEditionです。

Answer 4

ALTER TABLEでカラムを追加することができます。その際、既にある行に対して追加されたカラムに格納すべき値を指定する必要があります。幸い「初期値は0でよい」とのことですから、カラムのデフォルト値として 0 を指定して追加すればいいでしょう。
こうすることでデータを退避する必要はありません。

ただし、テーブルには本来カラムの順序という概念はないため、追加されたカラムは（たぶん）最後になります。カラムの順序を指定するのは、SELECTで明示的に記述するべきではあります。どうしてもカラムの順序込みでテーブルを定義したいのであれば、他の方がかかれているようにテーブルの作り直しが必要です。

Answer 5

> SQL Server 2012 ExpressEditionです。

了解しました。

回答者の回答に対するレスもよろしくお願いします。

Answer 6

ベストかどうかはあれですが、私がやるなら下記の様にやります。

BEGIN TRY
BEGIN TRANSACTION

IF EXISTS(SELECT * FROM sys.columns WHERE name = '列わ' AND object_id IN (SELECT object_id FROM sys.objects WHERE name = 'テーブル名'))
BEGIN
	RAISERROR (N'このデータベースは処理済です。', -- Message text.
           10, -- Severity,
           1, -- State,
           N'number', -- First argument.
           5); -- Second argument.
END
ELSE
BEGIN
	-- 退避
	SELECT *
	  INTO テーブル名_
	  FROM テーブル名
	-- 削除
	DROP TABLE テーブル名
	;

	CREATE TABLE テーブル名 (
		列あ varchar(1),
		列わ int,
		列い varchar(1),
		列う varchar(1),
	)
	;

	--主キーやインデックス再作成 ※あれば
	ALTER TABLE テーブル名
		ADD CONSTRAINT PK_テーブル名
		PRIMARY KEY (???)
	;
	
	INSERT INTO テーブル名
	SELECT 列あ
		 , 0
		 , 列い
		 , 列う
	  FROM テーブル名_

	DROP TABLE テーブル名_
END

COMMIT TRANSACTION
END TRY
BEGIN CATCH
	PRINT ERROR_MESSAGE()
	ROLLBACK TRANSACTION
END CATCH

Answer 7

今頃こういうことを言うのもなんですが・・・

identity 列とか timestamp 列とか rowversion 列とかがあって、それらを含めて完全にコピーしたいという場合は、既存の列に新しい列を割り込ませたテーブルを作るのは無理かもしれませんね。

やはり、佐祐理さんが書かれているように、テーブル内の列の順序は気にしないことにするのが正解かもしれません。

Answer 8

SQL Server Management Studioで、③「列あ」と「列い」の間に、「列わ」(int型）を挿入する。

補足しておきます。この場合、テーブルは再作成されます。ちなみに「列い」の後、すなわち最後尾に追加する場合は再作成は行われません。テーブルが巨大な場合、テーブルの再作成には時間がかかります。
なお、SQL Server 2008以降では、SQL Server Management Studioでテーブルの再生成を伴う変更を許可しておかないと、「列わ」を挿入することができません。デフォルトでは許可されていません。
すみません。舌足らずでした。この辺りは以下の資料を参考にして下さい。

（参考）
エラー メッセージ "変更の保存が許可されていません" が、SQL Server でテーブルを保存しようとすると表示される
http://support.microsoft.com/kb/956176/ja

なお、列の追加に際しては、以下に目を通されておかれると良いでしょう。

テーブルへの列の追加 (データベース エンジン)
http://msdn.microsoft.com/ja-jp/library/ms190238(v=sql.110).aspx

---

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

Answer 9

まとめての返信で申し訳ございません。

みなさんご教示ありがとうございました。

列を挿入する場合はManagement　Studioで行うこと（設定変更あり）、

ただし、列の挿入は例外的なもので、通常は列の順序に依存しないようにすること、などを学びました。

aviator_様のSQLも参考にさせていただきます。