Sql_ How do I insert Table A data for user A into table B of User B?

Statement ************************************************************** *********

Original works, from the "Blue Blog" blog, Welcome to reprint, please be sure to indicate the source, otherwise hold the copyright legal responsibility.

Deep Blue Blog:http://blog.csdn.net/huangyanlong/article/details/39901995

*************************************************************************************************************** *************

How do I implement User A's table a data into table B of User B?

Situation One: Table A, table B data structures are identical. Under User B, create a table that belongs to User B by using User A's table to achieve the same data for both tables.

Example: Hyl user creates a new table based on Scott's Dept table data, requiring the table a data to be the same as Scott's Dept table data
Sql> create user hyl identified by Hyl;
User created.
--Create an experimental user
Sql> Grant connect to Hyl;
Grant succeeded.
--Grant the appropriate permissions
Sql> Grant Select any table to Hyl;
Grant succeeded.

Sql> grant resource to hyl;
Grant succeeded.

Sql> Conn Hyl/hyl
Connected.

Sql> select * from tab;
No rows selected

Sql> CREATE TABLE A as SELECT * from Scott.dept;
Table created.

Sql> select * from tab;
Tname Tabtype Clusterid
------------------------------ ------- ----------
A TABLE

Sql> select * from A;
--created table A based on Scott's Dept data
DEPTNO dname LOC
---------- -------------- -------------
Ten ACCOUNTING NEW YORK
DALLAS
SALES CHICAGO
OPERATIONS BOSTON

Situation Two: Table A differs from table B in that it requires data from table B to cover the data in table a

Example: Hyl user's Table Newdept,scott Table dept, requires the data from the Dept table to be inserted into the Newdept table in the format of the new field

sql> CREATE TABLE Newdept (Newdeptno number (2), Olddeptno Number (2), Newdname varchar2 (+), Olddname varchar2 (+), Newloc varchar2 (+), Oldloc varchar2 (13));
Table created.
--create experiment Table
sql> select * from tab;
--View the table that Hyl currently owns
tname                           tabtype  Clusterid
------------------ -----------------------------
a                               TABLE
newdept                         TABLE

Sql> select * from Newdept;
--Query The experimental table, there is no data
No rows selected

sql> desc newdept;
--Query the table structure of the new table
Name Type Nullable Default Comments
--------- ------------ -------- ------- --------
Newdeptno Number (2) Y
Olddeptno Number (2) Y
Newdname VARCHAR2 Y
Olddname VARCHAR2 Y
Newloc VARCHAR2 (x) Y
Oldloc VARCHAR2 (x) Y

--inserting data into a new table against the structure of the new table
sql> INSERT INTO newdept (olddeptno,olddname,oldloc) Select T.deptno as olddeptno,t.dname as olddname,t.loc as Oldloc fr Om scott.dept t where t.deptno not in (select Olddeptno from newdept);
--inserting Scott's data into the Hyl user, requesting the location of the new field, and using the where constraint, the new data that is required to be inserted does not exist on the Olddeptno column of the new table
4 rows inserted

Sql> select * from Newdept;
--Query the data of the Newdept table now
Newdeptno olddeptno newdname olddname newloc oldloc
--------- --------- -------------- -------------- ------------- -------------
Ten ACCOUNTING NEW YORK
DALLAS
SALES CHICAGO
OPERATIONS BOSTON

Sql> select * from Scott.dept;
--look at the data in Scott's Dept table in contrast.
DEPTNO dname LOC
------ -------------- -------------
Ten ACCOUNTING NEW YORK
DALLAS
SALES CHICAGO
OPERATIONS BOSTON

--In contrast, the Newdept table under the Hyl User and the Dept table under the Scott user have imported the Dept data into the new Table Hyl table under the Newdept user as required.

--Verifying the amount of data in two tables
Sql> Select COUNT (*) from scott.dept;
COUNT (*)
----------
4

Sql> Select COUNT (*) from newdept;
COUNT (*)
----------
4

Statement ************************************************************** *********

Original works, from the "Blue Blog" blog, Welcome to reprint, please be sure to indicate the source, otherwise hold the copyright legal responsibility.

Deep Blue Blog:http://blog.csdn.net/huangyanlong/article/details/39901995

*************************************************************************************************************** *************

Sql_ How do I insert Table A data for user A into table B of User B?