Oracle----copying data from tables

Two methods:

1. When creating a table, copy the data from the other tables ( and the structure of the table is also copied over ):

Grammar:

　

CREATE TABLE table_name  as SELECT column1,...... |*  from Table_old;

Sql>SQL> Create Tableuserinfo_new2   as  3  SELECT *  fromuserinfo; The table was created. SQL> descuserinfo_new; is the name empty? type----------------------------------------- -------- ----------------------------Id Number(6) USERNAMEVARCHAR2( -) UserpwdVARCHAR2( -) EMAILVARCHAR2( -) regdate Datesql>

The values are also copied:

Sql> Select *  fromuserinfo_new; ID USERNAME userpwd---------- -------------------- --------------------EMAIL regdate------------------------------ --------------         1Xxx123456Ltwuyanlong@163. com --September- the         2yyy456123         3no ID USERNAME userpwd

Sql> descis the userinfo_new1 name empty? type----------------------------------------- -------- ----------------------------Id Number(6) USERNAMEVARCHAR2( -)SQL> Select *  fromUserinfo_new1; ID USERNAME---------- --------------------         1XXX2yyy3         4SQL>

-- -------- -------------------- -------------------- EMAIL                          RegDate--------------------------------------------         4631464569 @163 . Comsql >

2. When adding data, copy the data from the other tables:

Grammar:

INSERT  into table_name [(Column1,column2,...) ]Select column1,column2,..... |  from Table_old

In this case, you must require that both tables exist before you can use them!

Sql> Insert  intouserinfo_new2  Select *  fromuserinfo; 4 rows have been created. SQL> SelectId fromuserinfo_new; ID----------         1         2         3         4         1         2         3         48 rows have been selected. SQL>

Specify the Insert field:

Sql>SQL> Insert  intouserinfo_new (id,username)2  SelectId,username fromuserinfo; 4 rows have been created. SQL> SelectID, username fromuserinfo_new; ID USERNAME---------- --------------------         1XXX2yyy3         4         1XXX2yyy3         4         1XXX2yyy3ID USERNAME---------- --------------------         412 rows have been selected. SQL>

Oracle----copying data from tables