How do I copy all the columns from one row into a new row except one?

Question

I have this stored procedure, which has been working for a little while

CREATE PROCEDURE `ProjectFromTemplate`(
IN TemplateID INT,
IN NewProjectID INT
)
BEGIN
INSERT INTO Project_Stage
(
  ProjectID,
  StageID,
  StageIndex,
  Time
)
(
SELECT 
  NewProjectID,
  StageID,
  StageIndex,
  Time
  FROM Project_Stage
  WHERE ProjectID = TemplateID
);
END

But I decided to add another column to the table, but I didn't update the procedure accordingly. I would like the procedure to be able to handle any new rows that I add. I need something like this pseudo-SQL

CREATE PROCEDURE `ProjectFromTemplate`(
IN TemplateID INT,
IN NewProjectID INT
)
BEGIN
INSERT INTO Project_Stage
(
  ProjectID,
  *
)
(
SELECT 
  NewProjectID,
  *
  FROM Project_Stage
  WHERE ProjectID = TemplateID
);
END

Is there a way to express something like 'all the subsequent rows' in MySQL?

Duplicated [here](http://stackoverflow.com/questions/729489/duplicate-copy-records-in-the-same-mysql-table) and [here](http://stackoverflow.com/questions/11331573/mysql-copy-row-but-with-new-id) — vyegorov, Jul 05 '12 at 13:46

score 0 · Accepted Answer · answered Jul 05 '12 at 13:39

Leave out the column clause in your INSERT to minimize the code changes, but you have to spell out the rest of the columns from Project_Stage unless it doesn't contain a ProjectID column. If it doesn't, then your good.

Beware: you might still need to recompile after altering the table, though (I'm not sure how MySQL handles schema changes and compiled procs).

CREATE PROCEDURE `ProjectFromTemplate`(
    IN TemplateID INT,
    IN NewProjectID INT
)
BEGIN
    INSERT INTO Project_Stage    
    SELECT 
        NewProjectID,
        ...
        the rest of the Project_Stage columns you want go here
        ...
        FROM Project_Stage
        WHERE ProjectID = TemplateID
    );
END

This reduces the duplication, but I was really hoping for some way that would allow me to avoid manually spelling out every column — Peter Olson, Jul 05 '12 at 13:47
You can only avoid it if `Project_Stage` doesn't have a `ProjectID` column. Once you want to leave one column out, have to specify all the others. — bluevector, Jul 05 '12 at 13:52

How do I copy all the columns from one row into a new row except one?

1 Answers1