Copy all records between tables with the exception of a line

Question

I have a syntax that can copy all records from tables that have the same fields:

INSERT INTO table2 SELECT * FROM table1 WHERE id = '7'

So I would like to know how do I keep copying all values ​​except the 'id' field (because him is primary key and auto increment).

Answer 1

You list the columns:

insert into table2(col1, . . . coln)
    select col1, . . ., coln
    from table1
    where id = '7';

Under most circumstances, you should list the columns explicitly. You can run into problems even when tables have the same columns but in a different order.

(The one exception in code that I would write is in a script where table1 is created from table2 using create table as.)

Answer 2

You have to list all the columns specifically:

INSERT INTO table2 (col1, col2, col3, ...)
SELECT col1, col2, col3, ...
FROM table1
WHERE id = '7'

Answer 3

You'd need to specify the fields you're copying explicitly:

INSERT INTO table2 (col1, col2, col3, etc)
SELECT col1, col2, col3, etc 
FROM   table1 
WHERE  id = '7'