oracle 如何将一个模式表中的数据插入到另一个模式表中？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21304793/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can i insert the data in one schema table to another schema table?
提问by user2849710
I have two different schemas. Each schema contains different tables. For example a is a table in schema1 and b is table in schema2. Now i want to insert table a data into table b(schema2).
我有两种不同的模式。每个模式包含不同的表。例如，a 是 schema1 中的表，b 是 schema2 中的表。现在我想将表 a 数据插入表 b(schema2)。
回答by Dba
Login to the user which have access to both the schema and run insert command like,
登录到有权访问架构并运行插入命令的用户，例如，
INSERT INTO schema1.table_name
SELECT * FROM schema2.table_name;
Assuming that the tables are identical in both the schema.
假设两个模式中的表是相同的。
回答by Rob van Laarhoven
schema1 needs privileges on table in schema2
schema1 需要对 schema2 中的表的权限
connect schema2
grant select , insert on b to schema1;
Then
然后
connect schema1
insert into schema2.b select * from a;
Or create a synonym
或创建同义词
create synonym b for schema2.b;
insert into b select * from a;
回答by Rizwan Basheer
In order to insert in other schema in any database. first of all table needs to be created. below query will help you to build and copy data from one schema to another.
为了插入任何数据库中的其他模式。首先需要创建表。下面的查询将帮助您构建数据并将数据从一种模式复制到另一种模式。
below will create table only DDL.
下面将仅创建表 DDL。
CREATE TABLE Destinationschemaname.tablename AS SELECT * FROM sourceschemaname.tablename where 1 =2;
CREATE TABLE Destinationschemaname.tablename AS SELECT * FROM sourcesschemaname.tablename where 1 =2;
then DML Insert into Destinationschemaname.tablename SELECT * FROM sourceschemaname.tablename;
然后 DML 插入到 Destinationschemaname.tablename SELECT * FROM sourcesschemaname.tablename;
(OR)
below will copy complete table DDL and DML CREATE TABLE Destinationschemaname.tablename AS SELECT * FROM sourceschemaname.tablename;
下面将复制完整的表 DDL 和 DML CREATE TABLE Destinationschemaname.tablename AS SELECT * FROM sourcesschemaname.tablename;
