Assume I have a table as follows named
id is a Primary Key:
+-----------+---------+---------+ | id | fname | lname | | (integer) | (text) | (text) | +===========+=========+=========+ | 1 | Daniel | Edwards | | 2 | Fred | Holt | | 3 | Henry | Smith | +-----------+---------+---------+
I'm trying to write a row duplication query which is robust enough to account for schema changes to the table. Any time I add a column to the table, I don't want to have to go back and modify the duplication query.
I know I can do this, which will duplicate record id 2 and give the duplicated record a new id:
INSERT INTO people (fname, lname) SELECT fname, lname FROM people WHERE id = 2;
However if I add an
age column, I'll need to modify the query to also account for the age column.
Obviously I can't do the following, because it will also duplicate the primary key, resulting in a
duplicate key value violates unique constraint — And, I don't want them to share the same id anyway:
INSERT INTO people SELECT * FROM people WHERE id = 2
With that said, what would be a reasonable approach to solving this challenge? I would prefer to stay away from stored procedures, but I'm not 100% against them, I suppose …