Форум пользователей MySQL

drop table if exists form;
drop table if exists element;

create table form
(
id int not null auto_increment,
name varchar(200),
prop1 varchar(200),
prop2 varchar(200),
primary key(id)
);

create table element
(
id int not null auto_increment,
name varchar(200),
prop1 varchar(200),
prop2 varchar(200),
form_id int,
primary key(id),
CONSTRAINT `element_form_fk` FOREIGN KEY (form_id) REFERENCES form (ID) ON DELETE CASCADE
);

/*
inserting test values
*/

insert into form (name, prop1, prop2) value ('contact', '1', '2');
insert into element (name, prop1, prop2, form_id) values ('last_name', '1', '2', 1);
insert into element (name, prop1, prop2, form_id) values ('first_name', '1', '2', 2);
insert into element (name, prop1, prop2, form_id) values ('email', '1', '2', 3);

drop procedure if exists copy_form;

create procedure copy_form(src_form_id integer, new_form_name varchar(200))
    DETERMINISTIC
    SQL SECURITY INVOKER
begin

  declare new_from_id integer;

  insert into form (name, prop1, prop2)
  SELECT new_form_name, prop1, prop2 from form where id = src_form_id;

  set @new_from_id = last_insert_id();

  insert into element (name, prop1, prop2, form_id)
  SELECT name, prop1, prop2, @new_from_id from element
  where form_id = src_form_id;

end;

call copy_form(1, 'copy_of_contact');