求助，如何实现将一个数据库的表中符合条件的数据导入另一个表中？

Question

如题，例如学生学籍表，我原来将本科生和研究生的数据都放在一起的，现在我要分开，如何将硕士生的数据从原来的表里导出来，再导入到另一个表中？请各位给出解决方案，是oracle8i ,我不是很熟。

Answer 1

table1:id number,student_type number,name varchar(8)
table2:id number,name varchar(8)1.先创建表table2;
2.insert into table2 select a.id,a.name from table1 a where a.student_type=2;
假设研究生类别编号为2。

Answer 2

楼上的好像不对吧，执行后结果
语句：insert into ESJ select a.xh,a.xm,a.kh,a.xw,a.km,a.xq,a.cj,a.dfrq,a.xf,a.kx,a.bz,a.nj from sj a where a.xh like '%M%';
                                                                                                      
ERROR 位于第 1 行:
ORA-00911: 无效字符还有没有别的办法？可不可以用导入导出工具实现？如果用sql实现，正确的语句该是什么？

Answer 3

desc esj;
desc sj;
看一下两表的字段名称和类型。

Answer 4

ESJ:
名称                                      空?      类型
 ----------------------------------------- -------- -----------------
 XH                                        NOT NULL VARCHAR2(9)
 XM                                                 VARCHAR2(8)
 KH                                        NOT NULL VARCHAR2(6)
 XW                                                 VARCHAR2(2)
 KM                                                 VARCHAR2(44)
 XQ                                                 VARCHAR2(2)
 CJ                                                 VARCHAR2(2)
 DFRQ                                               VARCHAR2(5)
 XF                                                 VARCHAR2(4)
 KX                                                 VARCHAR2(4)
 BZ                                                 VARCHAR2(2)
 NJ                                                 VARCHAR2(2)SJ:
 名称                                      空?      类型
 ----------------------------------------- -------- ------------------
 XH                                                 VARCHAR2(9)
 XM                                                 VARCHAR2(8)
 KH                                                 VARCHAR2(6)
 XW                                                 VARCHAR2(2)
 KM                                                 VARCHAR2(44)
 XQ                                                 VARCHAR2(2)
 CJ                                                 VARCHAR2(2)
 DFRQ                                               VARCHAR2(5)
 XF                                                 VARCHAR2(4)
 KX                                                 VARCHAR2(4)
 BZ                                                 VARCHAR2(2)
 NJ                                                 VARCHAR2(2)

Answer 5

insert into ESJ(XH   ,
                XM   ,
                KH   ,
                XW   ,
                KM   ,
                XQ   ,
                CJ   ,
                DFRQ ,
                XF   ,
                KX   ,
                BZ   ,
                NJ   )
  values(select XH   ,
                XM   ,
                KH   ,
                XW   ,
                KM   ,
                XQ   ,
                CJ   ,
                DFRQ ,
                XF   ,
                KX   ,
                BZ   ,
                NJ  
          from EJ where 限定研究生的条件);

Answer 6

SQL> insert into ESJ(XH ,XM, KH, XW,KM,XQ,CJ,DFRQ ,XF,KX,BZ,NJ) values(select XH, XM,KH,XW,KM,XQ,CJ,
DFRQ,XF,KX,BZ,NJ from EJ where EJ.xh like '%M%');
insert into ESJ(XH ,XM, KH, XW,KM,XQ,CJ,DFRQ ,XF,KX,BZ,NJ) values(select XH, XM,KH,XW,KM,XQ,CJ,DFRQ,
                                                                  *
ERROR 位于第一行:
ORA-00936: 缺少表达式各位大哥,上面的方法还是不行,错误如上,请高手帮忙分析一下!还有没有好的办法?

Answer 7

看上面两张表的结构是一样的可以这样写
insert into ESJ （select * from EJ where EJ.xh like '%M%');

Answer 8

XH                                        NOT NULL VARCHAR2(9)
KH                                        NOT NULL VARCHAR2(6)
因有两个非空字段
insert into ESJ (select * from EJ where EJ.xh like '%M%' and EJ.KH is not null );

Answer 9

insert into table as select * from table2 where ...