CODEWITHSUNDEEP
sqloracle-databasecrud
bouncingHippo
bouncingHippo

Reputation: 6040

Create and copy a table from one SQL database to another

Let's say I have a production database and a development database.

The CUSTOMER_PERMISSIONS table is created and populated in the development database.

What is the best way to create and copy the CUSTOMER_PERMISSIONS table to the production database? 

INSERT INTO CUSTOMER_MARKET_PERMS
SELECT * FROM indeiso.dbo.CUSTOMER_MARKET_PERMS

returns

INSERT INTO CUSTOMER_MARKET_PERMS
SELECT * FROM inukiso.dbo.CUSTOMER_MARKET_PERMS
Error at Command Line:668 Column:25
Error report:
SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 -  "SQL command not properly ended"
*Cause:    
*Action:

I am using Oracle 12 database system, and i am not sure why it is throwing this error

Upvotes: 0

Views: 935

Answers (3)

Peter Å
Peter Å

Reputation: 1319

Are you sure that you have two different Oracle databases? If so then you need to setup a database link between the two databases. If I were you I should first make sure that you from the production database can select the data in the development database. If you can that and the two tables are identical then I can't see way your statement is not working.

Upvotes: 0

SchmitzIT
SchmitzIT

Reputation: 9552

If you're okay with not using a query, and in case you happen to be using MSSQL Server, why not use the import/export wizard?

Right-click the database you want to export from, then select TASKS -> Export Data. From there, follow the wizard.

Upvotes: 0

Z .
Z .

Reputation: 12837

on the prod server you can do a query like this:

INSERT INTO CUSTOMER_PERMISSIONS
SELECT * FROM [devserver].dbo.CUSTOMER_PERMISSIONS

Upvotes: 1

Related Questions