CODEWITHSUNDEEP
mysqlsql
Lalit Jadiya
Lalit Jadiya

Reputation: 213

Sql Copy table from One Database to Another Database

I need to copy a table from one database to another. So I tried to query in SqlFiddle as follows but getting error

CREATE TABLE ForgeRock
    (`id` int, `productName` varchar(7), `description` varchar(55))
;

INSERT INTO ForgeRock
    (`id`, `productName`, `description`)
VALUES
    (1, 'OpenIDM', 'Platform for building enterprise provisioning solutions'),
    (2, 'OpenAM', 'Full-featured access management'),
    (3, 'OpenDJ', 'Robust LDAP server for Java')
;


CREATE TABLE ForgeRock1 AS SELECT * FROM ForgeRock

Error:

DDL and DML statements are not allowed in the query panel for MySQL; only SELECT statements are allowed. Put DDL and DML in the schema panel.

Upvotes: 2

Views: 201

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522712

You can use the MySQL INSERT INTO...SELECT syntax to achieve what you want.

CREATE TABLE ForgeRock1
    (`id` int, `productName` varchar(7), `description` varchar(55));

INSERT INTO ForgeRock1 SELECT * FROM ForgeRock

Upvotes: 0

Cyclonecode
Cyclonecode

Reputation: 30121

You could also use mysqldump to dump a table into another database:

mysqldump -u<user> -p<password> <first_database> <table_name> | mysql -u<user> -p<password> <second_database>

Of course the second database must then first be created, which can be done using a command like:

mysql -u<user> -p<password> -e"CREATE DATABASE <second_database>"

Upvotes: 1

Related Questions