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

  • 質問

  • 失礼します。

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

    【やりたいこと】

    ①列を途中に挿入する。

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

    【内容】

    ①列あ、列い,列うがある(すべて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が検出されました

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

    【考察】

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

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

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

    2014年6月25日 8:30

回答

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

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

    update テーブル名 set 列わ = 0


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


    • 編集済み trapemiya 2014年6月25日 9:09 SQL訂正(ぼけてました)
    • 回答としてマーク アースワーム 2014年6月26日 5:42
    2014年6月25日 9:08
  • ベストかどうかはあれですが、私がやるなら下記の様にやります。

    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
    

    2014年6月26日 2:51
  • 今頃こういうことを言うのもなんですが・・・

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

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

    2014年6月26日 3:16
  • 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/

    2014年6月26日 4:58

すべての返信

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

    以下のページが参考になりませんか? (未検証です)

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

    2014年6月25日 9:03
  • >他の方法も含めて、ご教示ください。

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

    update テーブル名 set 列わ = 0


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


    • 編集済み trapemiya 2014年6月25日 9:09 SQL訂正(ぼけてました)
    • 回答としてマーク アースワーム 2014年6月26日 5:42
    2014年6月25日 9:08
  • 大変申し訳ございません。

    SQL Server 2012 ExpressEditionです。

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

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

    2014年6月25日 10:31
  • > SQL Server 2012 ExpressEditionです。

    了解しました。

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

    2014年6月26日 1:15
  • ベストかどうかはあれですが、私がやるなら下記の様にやります。

    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
    

    2014年6月26日 2:51
  • 今頃こういうことを言うのもなんですが・・・

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

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

    2014年6月26日 3:16
  • 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/

    2014年6月26日 4:58
  • まとめての返信で申し訳ございません。

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

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

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

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

    2014年6月26日 5:47