SQL文について質問です。


あるテーブルを元にして変更を加えた新テーブルを作成し、
元テーブルからデータをコピーします。
データコピーの際に、新テーブルで新たに追加した列へ
自動採番を行った値をinsertして行きたいのですが、方法がわかりません。

データは1件ずつではなく、一度の処理でコピーしたいです。
詳細手順は下記に示します。


CREATE TABLE hoge (hoge text);

上記のようなテーブルがあったとします。
このhogeテーブルを、hoge_idと言う列を持ったテーブルに変更します。
変更方法は下記の通りとします。

1.今あるhogeテーブル名をbk_hogeに変更
2.hoge_id列の定義を追加した新hogeテーブルを作成
CREATE TABLE hoge
(
hoge_id numeric(2),
hoge text,
CONSTRAINT hoge_pkey PRIMARY KEY (hoge_id)
);

3.bk_hogeに既にinsertされているデータを、新hogeテーブルにコピー
INSERT INTO hoge SELECT
★,
bk_hoge.hoge
FROM bk_hoge;

この★の部分で、データ1件ずつに対して自動採番を行った値を入れていきたいです。

よい方法がございましたらご教授願えれば幸いです。
よろしくお願い致します。

回答の条件
  • URL必須
  • 1人2回まで
  • 登録:2007/10/11 13:09:26
  • 終了:2007/10/12 10:42:48

ベストアンサー

id:Mook No.3

Mook回答回数1312ベストアンサー獲得回数3912007/10/11 15:14:46

ポイント50pt

使用しているDatabase は明記された方が良いと思います。


いろいろやり方はあると思いますが、PostgreSQL であればプロシージャを使用したやり方はどうでしょうか。

以下 SQL として実行します。



【コピー先テーブルの作成】

 CREATE TABLE bk_hoge (
    hoge_id NUMERIC(2) PRIMARY KEY,
    hoge TEXT );

【プロシージャの作成】

CREATE OR REPLACE FUNCTION copy_table() RETURNS INTEGER AS $$
DECLARE
    mv RECORD;
    num INTEGER;
BEGIN
    num := 0;
    FOR mv IN SELECT * FROM hoge ORDER BY hoge LOOP
        num := num + 1;
        INSERT INTO bk_hoge ( hoge_id, hoge ) values ( num, ''||mv.hoge||'' );
    END LOOP;
    RETURN num;
END;
$$ LANGUAGE 'plpgsql'

【プロシージャの実行】

select copy_table();

copy_table は新規テーブルに対して実行するのが前提ですので、再実行する前にテーブルを再作成してください。

(プロシージャの中でやっても良いのですが、あえて外に出しました。)


http://www.postgresql.jp/document/pg824doc/html/plpgsql.html

http://www50.tok2.com/home/oppama/pgfuncdef.html

不明な点はコメントで対応します。

id:yuri-jda

詳細な解説をありがとうございました。

「プロシージャ」というものを使用したことが無いので、

記載していただいた【プロシージャの作成】について解析させて頂きます。

見た事のない構文もあるので、質問させていただくこともあるかもしれませんが、

その際はどうぞよろしくお願い致します。

ありがとうございました。

2007/10/11 15:50:05

その他の回答(4件)

id:TONTON3 No.1

TONTON3回答回数212ベストアンサー獲得回数42007/10/11 13:20:23

ポイント11pt

http://homepage2.nifty.com/sak/w_sak3/doc/sysbrd/sq_kj01.htm

oracleであればsequence というのが使えますが対象は何になりますか?

id:yuri-jda

記載漏れとなり申し訳ございません。

使用環境はPostgresです。

2007/10/11 13:46:21
id:taknt No.2

きゃづみぃ回答回数13537ベストアンサー獲得回数11982007/10/11 13:24:57

ポイント11pt

オラクルでしたら ROWNUMを 入れたらいいでしょう。



http://q.hatena.ne.jp/1192075764

id:yuri-jda

INSERT INTO hoge SELECT

ROWNUM,

bk_hoge.hoge

FROM bk_hoge;

上記を実行しましたが、環境がPostgresの為か

ERROR: column "rownum" does not exist

が発生してしまいました。

PostgresにはROWNUMが無いようですね。残念です。

2007/10/11 13:56:41
id:Mook No.3

Mook回答回数1312ベストアンサー獲得回数3912007/10/11 15:14:46ここでベストアンサー

ポイント50pt

使用しているDatabase は明記された方が良いと思います。


いろいろやり方はあると思いますが、PostgreSQL であればプロシージャを使用したやり方はどうでしょうか。

以下 SQL として実行します。



【コピー先テーブルの作成】

 CREATE TABLE bk_hoge (
    hoge_id NUMERIC(2) PRIMARY KEY,
    hoge TEXT );

【プロシージャの作成】

CREATE OR REPLACE FUNCTION copy_table() RETURNS INTEGER AS $$
DECLARE
    mv RECORD;
    num INTEGER;
BEGIN
    num := 0;
    FOR mv IN SELECT * FROM hoge ORDER BY hoge LOOP
        num := num + 1;
        INSERT INTO bk_hoge ( hoge_id, hoge ) values ( num, ''||mv.hoge||'' );
    END LOOP;
    RETURN num;
END;
$$ LANGUAGE 'plpgsql'

【プロシージャの実行】

select copy_table();

copy_table は新規テーブルに対して実行するのが前提ですので、再実行する前にテーブルを再作成してください。

(プロシージャの中でやっても良いのですが、あえて外に出しました。)


http://www.postgresql.jp/document/pg824doc/html/plpgsql.html

http://www50.tok2.com/home/oppama/pgfuncdef.html

不明な点はコメントで対応します。

id:yuri-jda

詳細な解説をありがとうございました。

「プロシージャ」というものを使用したことが無いので、

記載していただいた【プロシージャの作成】について解析させて頂きます。

見た事のない構文もあるので、質問させていただくこともあるかもしれませんが、

その際はどうぞよろしくお願い致します。

ありがとうございました。

2007/10/11 15:50:05
id:KUROX No.4

KUROX回答回数3542ベストアンサー獲得回数1402007/10/11 15:31:04

ポイント11pt

http://homepage2.nifty.com/lightbox/VA003334/dbaccess05090717281...

http://www.itmedia.co.jp/enterprise/0307/18/epn20_6.html

Postgresでも、sequence使えるようですよ。

sequenceを作成するのが手堅いような。

id:yuri-jda

自動採番について、色々なDB条件で記載されていて

今回以外にも参考にさせて頂けそうです。

ありがとうございました。

2007/10/11 17:31:52
id:chuken_kenkou No.5

chuken_kenkou回答回数722ベストアンサー獲得回数542007/10/11 15:53:46

ポイント17pt

PostgreSQLでの自動採番は、連番型(SERIAL型)を使います。

CREATE SEQUENCEも実装されており、SERIAL型は等価です。

1.テーブル名の変更

-- リネーム
ALTER TABLE hoge
 RENAME TO bk_hoge;

2.新テーブルの作成

2.1 新テーブルの定義

-- 新テーブル
CREATE TABLE hoge
(hoge_id   SERIAL,   -- PostgreSQLでの連番型。CREATE SEQUENCEも使用可能
 hoge      TEXT,
 CONSTRAINT hoge_pkey PRIMARY KEY (hoge_id)
);

連番型を操作する場合、「シーケンスの名前」を指定する必要があります。CREATE SEQUENCEを使う場合は、名前はユーザ指定になりますが、SERIAL型で定義時は、「表名_列名_seq」になります。

上記の表定義なら、「hoge_hoge_id_seq」という名前で生成されます。

2.2 データのコピー

(1)連番の初期化

 連番型を持つ表を定義した直後なら、初期化は自動的に行われます。

 テスト等でリセットしたい場合は、次のような操作を行います。

TRUNCATE TABLE hoge;
SELECT SETVAL('hoge_hoge_id_seq',1,FALSE);

(2)データのコピー<指定例1>

 連番型の列を除いてINSERTする。

INSERT INTO hoge(hoge)    -- 連番型以外の列を指定
 SELECT hoge FROM bk_hoge;

(3)データのコピー<指定例2>

 NEXTVAL関数を使う。

INSERT INTO hoge
 SELECT
   NEXTVAL('hoge_hoge_id_seq'),
   hoge
  FROM bk_hoge;

id:yuri-jda

詳細な解説をありがとうございました。

但し、今回の場合は列自体に自動採番の制約をかけたくなかったので

教えて頂いた方法は、別の機会に参考にさせて頂きます。

ひとつ気になったのですが、

  (2)データのコピー<指定例1>

   連番型の列を除いてINSERTする。

では、hoge_idがPkeyとなっているので

他の列の値を設定しようとするとエラーが出ないでしょうか・・・?

似たような方法を試したのですが、エラーが出てしまいました。

SERIAL制約ではないテーブルでしたし、

私の文法が違ったのかもしれませんが。。。

2007/10/11 17:29:25
  • id:chuken_kenkou
    >ひとつ気になったのですが、
    > (2)データのコピー<指定例1>
    >  連番型の列を除いてINSERTする。
    >では、hoge_idがPkeyとなっているので
    >他の列の値を設定しようとするとエラーが出ないでしょうか・・・?

    「プライマリキーにnull値を設定しようとする」という動きでなく、連番に
    +1するという動きになるので大丈夫ですよ。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません