Thesql copy and insert rows in same table with new primary key and sequence number

MySQL

I have multiple table with primary key, data columns and batch no.

I require procedure which dynamically creates statement to copy rows from table and insert into same table by changing auto increment value batch no.

Original Table

enter image description here

mysql query should copy records of batch 1 and paste them as batch 10 as below

enter image description here

I have tried to write query but it is coping all records, also it gets failed timeout for large no of records. 

CREATE PROCEDURE `duplicateRows`(_schemaName text, _tableName text, _omitColumns text, var_run_seq_no int,var_old_run_seq_no int)
    BEGIN
SELECT IF(TRIM(_omitColumns) <> '', CONCAT('id', ',', TRIM(_omitColumns),'batch_no'), CONCAT('id',',batch_no')) INTO @omitColumns;

SELECT GROUP_CONCAT(COLUMN_NAME) FROM information_schema.columns 
WHERE 
table_schema = _schemaName 
AND 
table_name = _tableName 
AND 
FIND_IN_SET(COLUMN_NAME,@omitColumns) = 0 
ORDER BY ORDINAL_POSITION INTO @columns;

SET @sql = CONCAT('INSERT INTO ', _tableName, '(', @columns, ',batch_no)',
  'SELECT ', @columns, ',', var_batch_no,
  ' FROM ', _schemaName, '.', _tableName);

-- select @sql;
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;
END

Best Answer

Suppose we have the following table, containing test data as specified in your question. Notice that the id column is "AUTO_INCREMENTed". (MySQL version 5.7)

create table batches (
  id int auto_increment primary key
, first_name varchar(32)
, last_name varchar(32)
, address varchar(32)
, batch_no integer
);

insert into batches (first_name, last_name, address, batch_no) values
 ('william','Faulkner','UK',1), ('suzy','nicholus','US',1)
,('ganesh','bora','india',1), ('mahadev','patil','india',1)
,('prasad','k','UK',2), ('sandeep','g','US',2)
,('prajakta','J','india',2), ('Nimish','G','india',2);

select * from batches;
+----+------------+-----------+---------+----------+
| id | first_name | last_name | address | batch_no |
+----+------------+-----------+---------+----------+
|  1 | william    | Faulkner  | UK      |        1 |
|  2 | suzy       | nicholus  | US      |        1 |
|  3 | ganesh     | bora      | india   |        1 |
|  4 | mahadev    | patil     | india   |        1 |
|  5 | prasad     | k         | UK      |        2 |
|  6 | sandeep    | g         | US      |        2 |
|  7 | prajakta   | J         | india   |        2 |
|  8 | Nimish     | G         | india   |        2 |
+----+------------+-----------+---------+----------+

The following procedure may help you to find a solution that fits your purpose. Your initial idea - assembling a combination of an INSERT and a SELECT, and executing it - is still in there, just the method of finding all necessary columns is different.

delimiter //

create procedure duplicate_rows (
  schema_ text
, table_ text
, oldbatch_ int
, newbatch_ int)
begin

  declare colINSERT_ text default '' ; -- columns needed for INSERTing
  declare colSELECT_ text default '' ; -- columns needed for SELECTing
-- Find all columns for INSERT and SELECT, respectively. () contain "insert" cols.
-- Exclude the 'id' and 'batch_no' columns from the column list(s).
  select 
      concat( '(', group_concat(column_name), ',batch_no)') 
    , concat( group_concat(column_name), ',', newbatch_ )
    into colINSERT_, colSELECT_ 
    from information_schema.columns 
   where table_schema = schema_
     and table_name = table_
     and column_name not in ('id', 'batch_no'); 

  set @sql = concat(
    'insert into '       , table_, colINSERT_
  , ' select '           , colSELECT_ 
  , ' from '             , table_
  , ' where batch_no = ' , oldbatch_
  );

  prepare stmt_ from @sql;
  execute stmt_;
  set @sql = '';

end//

delimiter ;

Test

mysql> call duplicate_rows('test', 'batches', 1, 20);
Query OK, 0 rows affected (0.00 sec)

mysql> select * from batches;
+----+------------+-----------+---------+----------+
| id | first_name | last_name | address | batch_no |
+----+------------+-----------+---------+----------+
|  1 | william    | Faulkner  | UK      |        1 |
|  2 | suzy       | nicholus  | US      |        1 |
|  3 | ganesh     | bora      | india   |        1 |
|  4 | mahadev    | patil     | india   |        1 |
|  5 | prasad     | k         | UK      |        2 |
|  6 | sandeep    | g         | US      |        2 |
|  7 | prajakta   | J         | india   |        2 |
|  8 | Nimish     | G         | india   |        2 |
|  9 | william    | Faulkner  | UK      |       20 |
| 10 | suzy       | nicholus  | US      |       20 |
| 11 | ganesh     | bora      | india   |       20 |
| 12 | mahadev    | patil     | india   |       20 |
+----+------------+-----------+---------+----------+
12 rows in set (0.00 sec)

Notice: when using this procedure, you may encounter "gaps" in auto-incremented ID columns. More information about this:

https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html

https://stackoverflow.com/questions/29315012/what-if-auto-increment-gaps-caused-by-mysql-insert-on-duplicate-key-update

https://stackoverflow.com/questions/1841104/how-to-fill-in-the-holes-in-auto-incremenet-fields

Related Question