Cannot get select into to work

Question

I have a database squid with a table configs.

I am trying to get the configs table into a different database [testsquid] on the same host.

Here is what I've tried:

select * into testsquid.configs from squid.configs;

I tried first without creating the configs table in testsquid, and then I actually created the configs table in testsquid.

I am trying to get the configs table from squid into the new database testsquid. I decided not to use mysql dump because it locks the table.

What am I doing incorrectly?

Paul Spiegel · Accepted Answer · 2016-03-27T15:24:46.317

0

If the table schemas are equal you can try:

insert into testsquid.configs
    select * from squid.configs;

If the new table does not exist yet create it with:

create table testsquid.configs like squid.configs;

Update:

I am not sure if insert-select also locks the table. To keep the locking time short you could create a similar temporary table with memory engine without any indexes. Copy the data into the temp table and after that from the temp table into the physical one.

edited Mar 27 '16 at 15:24

answered Mar 27 '16 at 14:49

Paul Spiegel

30,925
5
44
53

ok and how do i get the data into the new testsquid configs table? – slicks1 Mar 27 '16 at 14:54
Use the first query to transfer data. – Paul Spiegel Mar 27 '16 at 14:55

Cannot get select into to work

1 Answers1