时间戳

首页 > 解决方案 > Oracle 11g：将列从一个表复制到另一个添加序列 ID。删除序列

sql - Oracle 11g：将列从一个表复制到另一个添加序列 ID。删除序列

问题描述

我想将列值从一个表复制到另一个表中，例如（不起作用）：

create sequence INSTITUTION_SEQ
minvalue 1
maxvalue 999999999999999999999
start with 1
increment by 1
cache 20;

INSERT INTO INSTITUTION 
(ID, NAME)
VALUES(
INSTITUTION_SEQ.nextval, 
SELECT DISTINCT ACB_BANK_NAME FROM LUP ORDER BY ACB_BANK_NAME
);

问题

  1. 为了得到预期的结果，正确的 SQL 查询是什么？
  2. 如何在脚本末尾删除序列？尝试DROP INSTITUTION_SEQ但导致ORA-00950: invalid DROP option

标签： sqloracle11g

解决方案

您可以使用以下方法获取顺序值，而不是创建/使用/删除序列：

INSERT INTO INSTITUTION (ID, NAME)
SELECT ROW_NUMBER() OVER (ORDER BY ACB_BANK_NAME), ACB_BANK_NAME 
FROM
 ( -- DISTINCT is processed after ROW_NUMBER
   SELECT DISTINCT ACB_BANK_NAME FROM LUP
 ) dt;

INSERT INTO INSTITUTION (ID, NAME)
SELECT ROW_NUMBER() OVER (ORDER BY ACB_BANK_NAME), ACB_BANK_NAME 
FROM LUP
GROUP BY ACB_BANK_NAME; -- GROUP BY is processed before ROW_NUMBER

推荐阅读