I’m trying to move old data from:
this_table >> this_table_archive
copying all columns over. I’ve tried this, but it doesn’t work:
INSERT INTO this_table_archive (*) VALUES (SELECT * FROM this_table WHERE entry_date < '2011-01-01 00:00:00');
Note: the tables are identical and have
id set as a primary key.
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
The correct syntax is described in the manual. Try this:
INSERT INTO this_table_archive (col1, col2, ..., coln) SELECT col1, col2, ..., coln FROM this_table WHERE entry_date < '2011-01-01 00:00:00';
If the id columns is an auto-increment column and you already have some data in both tables then in some cases you may want to omit the id from the column list and generate new ids instead to avoid insert an id that already exists in the original table. If your target table is empty then this won’t be an issue.
For the syntax, it looks like this (leave out the column list to implicitly mean “all”)
INSERT INTO this_table_archive SELECT * FROM this_table WHERE entry_date < '2011-01-01 00:00:00'
For avoiding primary key errors if you already have data in the archive table
INSERT INTO this_table_archive SELECT t.* FROM this_table t LEFT JOIN this_table_archive a on a.id=t.id WHERE t.entry_date < '2011-01-01 00:00:00' AND a.id is null # does not yet exist in archive
Addition to Mark Byers answer :
Sometimes you also want to insert Hardcoded details else there may be Unique constraint fail etc. So use following in such situation where you override some values of the columns.
INSERT INTO matrimony_domain_details (domain, type, logo_path) SELECT 'www.example.com', type, logo_path FROM matrimony_domain_details WHERE id = 367
Here domain value is added by me me in Hardcoded way to get rid from Unique constraint.
don’t you need double () for the values bit? if not try this (although there must be a better way
insert into this_table_archive (id, field_1, field_2, field_3) values ((select id from this_table where entry_date < '2001-01-01'), ((select field_1 from this_table where entry_date < '2001-01-01'), ((select field_2 from this_table where entry_date < '2001-01-01'), ((select field_3 from this_table where entry_date < '2001-01-01'));
INSERT INTO vendors ( name, phone, addressLine1, addressLine2, city, state, postalCode, country, customer_id ) SELECT name, phone, addressLine1, addressLine2, city, state , postalCode, country, customer_id FROM customers;