INSERT INTO SELECT for all MySQL columns

Question

I m trying to move old data from   this table  gt  gt  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  lt   2011-01-01 00 00 00      Note  the tables are identical and have id set as a primary key

User · Answer

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  lt   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  lt   2011-01-01 00 00 00    AND a id is null    does not yet exist in archive

User · Answer

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  lt   2001-01-01       select field 1 from this table where entry date  lt   2001-01-01       select field 2 from this table where entry date  lt   2001-01-01       select field 3 from this table where entry date  lt   2001-01-01

User · Answer

More Examples  amp  Detail      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

User · Answer

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

User · Answer

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

[mysql] INSERT INTO...SELECT for all MySQL columns

Examples related to mysql

Examples related to select

Examples related to insert-into