oracle-从一个表将数据插入到另一个表、自动插入序列、表数据结构不同

--临时表结构


create table RECOMMEND_SERVICE_TMP
(
ID NUMBER not null,
MEMBER_ID NUMBER not null,
COMPANY_ID NUMBER not null,
RECOMMEND_SERVICE NUMBER not null
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);


--插入数据表结构

create table RECOMMEND_SERVICE
(
ID NUMBER not null,
GMT_CREATE DATE not null,
GMT_MODIFIED DATE not null,
MEMBER_ID NUMBER not null,
COMPANY_ID NUMBER not null,
RECOMMEND_SERVICE NUMBER not null
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);


--数据

insert into RECOMMEND_SERVICE_TMP (ID, MEMBER_ID, COMPANY_ID, RECOMMEND_SERVICE)
values (1, 1, 1, 1);
insert into RECOMMEND_SERVICE_TMP (ID, MEMBER_ID, COMPANY_ID, RECOMMEND_SERVICE)
values (2, 2, 2, 2);
insert into RECOMMEND_SERVICE_TMP (ID, MEMBER_ID, COMPANY_ID, RECOMMEND_SERVICE)
values (3, 3, 3, 3);
insert into RECOMMEND_SERVICE_TMP (ID, MEMBER_ID, COMPANY_ID, RECOMMEND_SERVICE)
values (4, 4, 4, 4);
insert into RECOMMEND_SERVICE_TMP (ID, MEMBER_ID, COMPANY_ID, RECOMMEND_SERVICE)
values (5, 5, 5, 5);
commit;

--插入sql语句

insert into recommend_service
(id, gmt_create, gmt_modified, member_id, company_id, recommend_service)
(select seq_xm_test001.nextval as id,
sysdate,
sysdate,
member_id,
company_id,
recommend_service
from recommend_service_tmp)


--取系统时间和序列

select seq_bureau_test_req_id.nextval as id from dual; -- 通过系统表(dual)一次只能取出一个序列
select seq_bureau_test_req_id.nextval as id from mail; -- 通过用户自定义的表(mail) 取序列，则表中有多少条数据就可以取多少个序列
select sysdate from files; --files是用户创建的任意一张表=,表中不包含sysdate字段