Oracle copies table data and copies table structure

 

1. Table data replication between different users
For two users A and B in a database, if you need to copy the old data in Table A to the new data in Table B, use a user with sufficient permissions to log on to sqlplus:
Insert into B. New (select * from A. Old );

If you need to add conditions, such as copying a. Old data of the current day
Insert into B. New (select * from A. Old where date = GMT );
Select conditions at a blue slash

2. Data replication between user tables
User B has two tables: B. X and B. Y. to transfer data from Table X to table y, use user B to log on to sqlpus:
Insert into target table y select * from X where log_id> '2013' -- Copy Data
Note: To display the target table y, you must create it in advance.
For example, insert into bs_log2 select * From bs_log where log_id> '123'

3. Some fields in B. X are transferred to the same field of B. Y.
-- If the two tables have the same structure
Insert into table_name_new select * From table_name_old
If the two tables have different structures:
Insert into Y (Field 1, Field 2) Select Field 1, Field 2 from X

4. If only the copy table structure is added with a condition 1 = 2 that is never possible, it indicates that only the table structure is copied, but the table content is no longer displayed.
Create Table username. Table name as select * from username. Table name where 1 = 2
For example, create table zdsy. bs_log2 as select * From zdsy. bs_log where 1 = 2

5. Fully copy the table (including creating and copying records in the table)
Create Table Test as select * From bs_log -- bs_log is the copied table

6. insert data from multiple tables into one table
Insert into target table test (Field 1... Field N) (select Field 1 ..... Field N) from Table Union all select Field 1... field N from table

========================================================== ==================
Comparison of complex tabulation in Oracle and MSSQL

Intra-Database Data Replication
Ms SQL Server:
Insert into copy table name SELECT statement (copy table already exists)
Select field list into copy table name from table (copy table does not exist)

ORACLE:
Insert into copy table name SELECT statement (copy table already exists)
Create Table copy table name as select statement (the copy table does not exist)

Update and delete multiple tables

An update statement cannot update multiple tables. Unless the trigger is used for implicit update, I mean: update the table to be updated based on the data of other tables. The general form is as follows:
Ms SQL Server
Update aset Field 1 = B Table field expression, Field 2 = B Table field expression from bwhere logical expression

Oracle
Update aset Field 1 = (select field expression from B where...), Field 2 = (select field expression from B where...) Where logical expression
From the above, I feel that Oracle does not have a good ms SQL, mainly because: If a requires multiple field updates, the ms_ SQL statement is more concise. You know how the person who just learned the database is doing the above.

Are they processing the cursor one by one?

===== Import === export ==============
(1) Export
Exp FF/FF @ orcl file = 'd: FF. dmp 'tables = customers direct = y
Use exp output. Enter the account and password of the User table to be backed up. Press enter as prompted to press OK. Then a ff. dmp file will appear, which is the backup data.
Export: 1. the entire database (the DBA permission is required); 2. users (including tables, views, and others); 3. tables (only tables are included, and views are not exported );

(2) Import
Create user ly identified by PW default tablespace users quota 10 m on users;
Create New User Username: ly password: pw default tablespace for this space, the quota is 10 m
Grant connect, resource, DBA to ly;
Grant the ly permission (1. connection; 2. Resource; 3. DBA permission. Import can only be performed !)
Grant create session, create table, create view, unlimited tablespaces to ly;
Grant other common permissions of LY (1. log on to the server, 2. Create a table, 3. Create a view, and 4. Unlimited tablespace)
IMP ly/ly @ orcl fromuser = FF touser = ly file = 'd: FF. dmp 'constraints = N
Use imp input. Enter the username and password of the user to be imported and press Enter. Follow the prompts until you enter the username again.

========================

SQL _server copies tables between different databases

Replication of table structures and data in different databases:
When the target database does not have the table to be imported:
Example:
Xuexiao is the target database, teaching is the source database, and DBO. course_list already exists in teaching. you want to copy the following statement to the xuexiao database that does not have this table.

:
Select * into xuexiao. DBO. course_list from teaching. DBO. course_list

How to copy table data between different databases

When the target table exists:
Insert into destination database... table select * from source database... table

If the target table does not exist:
Select * into destination database... table from source database... table
========================================================== ==========
As shown in the following figure, Table A is an existing table in the database, and table B is the table to be copied and created based on table:

1. copy only the SQL statements of the Table Structure
Create Table B as select * from a where 1 <> 1

2. SQL statement used to copy the table structure and data in the table
Create Table B as select * from

3. Copy the SQL statement used to create fields in the table
Create Table B as select row_id, name, age from a where 1 <> 1 // The premise is that row_id, name, and age are all columns in table.

4. Copy the specified fields of the table and the SQL statement of the data of these specified fields
Create Table B as select row_id, name, age from

Although the preceding statements can easily replicate and Create Table B according to the structure of Table A, the index of Table A cannot be copied and must be manually created in table B.

5. Insert into saves the query result to an existing table.
Insert into T2 (column1, column2,...) Select column1, column2,... from T1

1. How to obtain a single table and index DDL statements:

-----------------------------------------------------------------------

Set heading off;

Set echo off;

Set pages 999;

Set long 90000;

　

Spool get_single. SQL

Select dbms_metadata.get_ddl ('table', 'szt _ pqso2 ', 'shqsys') from dual;

Select dbms_metadata.get_ddl ('index', 'indxx _ pqzjyw ', 'shqsys') from dual;