oracle 修改已存在的表的自增字段数据

需求：

有一张表已经存在数据库，但是没有主键，没有自增列，导致做修改等操作的时候较为繁琐，现在要修改其ID列为自增（填充数据）

修改之前：

修改之后：

oracle 修改已存在的表的自增字段数据

方法：

1.复制表结构

2.若无自增字段，先增加自增id字段

3.创建触发器

4.将老数据转移到新的表

5.删除原表，将新表改名

CREATE TABLE TEST1 AS SELECT * FROM REPAIR_FACTORY WHERE 1<>1
ALTER TABLE "TEST1" ADD ID NUMBER
ALTER TABLE "TEST1" ADD PRIMARY KEY ("ID");

DROP TRIGGER TRG_BEFORE_INSERT_TEST1
CREATE TRIGGER TRG_BEFORE_INSERT_TEST1  BEFORE INSERT ON TEST1
FOR EACH ROW
BEGIN
    SELECT
       SEQ_REPAIR_FACTORY_ID.NEXTVAL INTO :NEW.ID
    FROM
        DUAL ;
    END ;

INSERT INTO TEST1("NAME", "PROVINCE", "CITY","CODE","PERSON","FORM","RIQI","MONEY","ADDRESS","MAIL","FANWEI","NET","TEL","TEL_MORE","LAT","LNG","FANWEI_TAG","NAME_PRE","NAME_MIDDLE","NAME_TYPE","TYPE_NORM","TAG","MATCH_NAME","MATCH_LAT","MATCH_LNG","MATCH_ADDRESS","GRADE","BJ_CATE","YQ_CATE","CZ_CATE","HJ_CATE","GRADE_ID","AREA_ID","PROVINCE_ID") 
SELECT a.name,a.province,a.city,a.code,a.person,a.form,a.riqi,a.money,a.address,a.mail,a.fanwei,a.net,a.tel,a.tel_more,a.lat,a.lng,a.fanwei_tag,a.name_pre,a.name_middle,a.name_type,a.type_norm,a.Tag,a.match_name,a.match_lat,a.match_lng,a.match_address,a.grade,a.bj_cate,a.yq_cate,a.cz_cate,a.hj_cate,a.grade_id,a.area_id,a.province_id FROM REPAIR_FACTORY a order by a.area_id,a.province_id

COMMIT;

注意：

1.转移数据的时候最好需要将每个字段都写出来，因为目标表少了一个id字段，若不是这么做，会报错

2.由于字段较多，要仔细检查。

oracle 修改已存在的表的自增字段数据

需求：

方法：

注意：

相关推荐