Re: How to make small tables from big table? (SELECT INTO doesn't seem to work)

[Fran Lee]

1 - Re: mysql does not support SELECT INTO

Try this in mySQL Workbench:

SELECT * INTO new_table FROM big_table LIMIT 5 OFFSET 0;

I get the error message: "Error Code 1327 Undefined variable new_table"

And, others seem to have had the same problem and are saying it is not supported

See this thread in Stack Overflow

https://stackoverflow.com/questions/32219183/mysql-does-not-support-select-into


2 - CREATE TABLE vs CREATE TABLE LIKE

CREATE TABLE new_table AS SELECT * FROM big_table LIMIT 5 OFFSET 0;

does not create the new table with the Private Key and Auto Increment) set for the record_id field

While CREATE TABLE LIKE does create the new table with the exact same table & field definitions as the original... then you can copy the data over with the INSERT INTO as below...

CREATE TABLE test2 LIKE space_events;
INSERT INTO testtest SELECT * FROM space_events LIMIT 5 OFFSET 0;

try them both in mySQL Workbench and click on the wrench by each of the new tables and you will see PK and AI are not created with CREATE TABLE but are with CREATE TABLE LIKE