CODEWITHSUNDEEP
sqloracle-database
CompanyDroneFromSector7G
CompanyDroneFromSector7G

Reputation: 4517

Copy tables structure and data to different user

I have a set of tables with data in one user schema. I want a copy of those to another user.

Can this be done with SQL?

Thanks

Upvotes: 1

Views: 2248

Answers (5)

knagaev
knagaev

Reputation: 2967

May be "CREATE TABLE ... AS SELECT" will be useful for you?

See docs

Here is a good article

Upvotes: 0

Orangecrush
Orangecrush

Reputation: 1990

If the two users are on the same database, and provided sufficient GRANTS are present, then just run the below statement in the new user schema:

create table <tablename> as select * from olduser.<tablename>;

EDIT:

Copy the below set of statements in a text file. Replace <oldueser> with the existing schema name and <path> with a path in the unix/windows machine where you want the spool to be written. Run this file in the existing schema. A spool file will be written in the path specified with the name tbls.sql. Take this tbls.sql and run it in the new schema, where you want the tables to be replicated.

set head off
set line 100000
set line 100000
set feedback off
spool <path>/tbls.sql
select 'CREATE TABLE ' || TABLE_NAME ||' AS SELECT * FROM <OLDUSER>.'||TABLE_NAME||';'
FROM user_tables; 
spool off
set feedback on

Upvotes: 1

GeorgeVremescu
GeorgeVremescu

Reputation: 1253

  1. You can export the first schema and import in the second schema - I would go for this if there are not any reasons preventing you to do that. Documentation is here.

  2. Provided you have access grants, you can write a small script to do that for you. You can select all your source user's tables using the query:

    SELECT table_name FROM all_tables WHERE owner = <source_schema>

Loop through all of them and create the new tables by using execute immediate. (Documentation here)

execute immediate 'create <dest>.<tablename> as select * from <source>.<tablename>';

dest and source are the name of the schema. tablename is the name of the table as taken from the loop.

Upvotes: 0

Naveen Kumar Alone
Naveen Kumar Alone

Reputation: 7678

Yes you can by using export command in command prompt i.e.

c:\>exp userid=userid(sql admin)/password owner=username(from which user) direct=y file=filename.dmp

to import this by using

c:\>imp userid=userid(sql admin)/password file=filename.dmp fromuser=username(from which user) touser=username(imported username)

by using this you have to export data into a dump file and after taht import it into another user

Upvotes: 0

paul
paul

Reputation: 22011

create schema2.tablename as select * from schema1.tablename

Upvotes: 0

Related Questions