Copy a Table Row in Server Connect Action

Server Side Components
#1

Is there a simple way to copy a table row within a server connect action without needing to read it in and then explicitly allocate each column from the read row into the copy’s columns?

I have a stored procedure that does the job, but am keen to do as much as I can in server actions.

#2

Do you want to copy some records from one database table to another?

#3

No, within the same table, so all the columns are the same…

#4

Well you will need to create a database query, get the record values and then run a repeat with database update within it.

How would I go about duplicating records in a database with server connect (php)
#5

One way is to use a single custom query:

INSERT INTO yourTable (col1, col2, col3, col4) 
SELECT
col1,
col2,
col3,
1
FROM
	yourTable 
WHERE
	id = :P1

In the example, I’m duplicating the first three columns and adding a default value to the fourth. The id passed to the query is the row you want to duplicate.

2 Likes
#6

Thanks for your feedback folks!

I’ll stick with the little stored procedure I’ve been using then, which doesn’t need me to specify each and every column name… here is an example:

DROP PROCEDURE IF EXISTS copy_question;
DELIMITER //
CREATE PROCEDURE copy_question ( question_in INT UNSIGNED, new_activity INT UNSIGNED ) 
this_procedure: BEGIN
	DROP TABLE IF EXISTS temp; CREATE TEMPORARY TABLE temp  
		SELECT * FROM questions WHERE id = question_in LIMIT 1;
	UPDATE temp SET id=0; UPDATE temp SET activity=new_activity; 
	INSERT INTO questions SELECT * FROM temp; DROP TABLE IF EXISTS temp;
END 
// DELIMITER ;
1 Like