Sql Copy table from One Database to Another Database

Question

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.

As the error states, SQLFiddle doesn't let you put CREATE TABLE statements in the box on the right. Put it in the box on the left. sqlfiddle.com/#!9/2f1c9/1 — Mark Leiber
– Mark Leiber, Commented Jul 3, 2015 at 5:24

Cyclonecode · Accepted Answer · 2015-07-03 05:28:31Z

1

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>"

edited Jul 3, 2015 at 5:28

answered Jul 3, 2015 at 5:27

Cyclonecode

30.4k11 gold badges77 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tim Biegeleisen Over a year ago

I think her question is more of a SQL Fiddle problem rather than a MySQL question. Too bad the title doesn't mention this.

Tim Biegeleisen · Accepted Answer · 2015-07-03 05:31:06Z

0

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

edited Jul 3, 2015 at 5:31

answered Jul 3, 2015 at 5:25

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

1 Comment

Tim Biegeleisen Over a year ago

@LalitJadiya I updated my answer so that it runs in SQL Fiddle. Try again. You need to put the INSERT statement on the left.

Collectives™ on Stack Overflow

Sql Copy table from One Database to Another Database

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related