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

Question

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

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

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

Answer 1

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

Answer 2

I am proposing a new design that will normalize the customers in each batch

DROP DATABASE IF EXISTS ganeshbora;
CREATE DATABASE ganeshbora;
USE ganeshbora
CREATE TABLE customer
(
    cust_id INT NOT NULL AUTO_INCREMENT,
    firstname VARCHAR(20),
    lastname  VARCHAR(20),
    address   VARCHAR(40),
    PRIMARY KEY (cust_id)
) ENGINE=InnoDB;
CREATE TABLE batch_sequence
(
    batch_id INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (batch_id)
) ENGINE=MyISAM;
CREATE TABLE batches
(
    batch_id INT NOT NULL,
    cust_id INT NOT NULL,
    PRIMARY KEY (batch_id,cust_id),
    UNIQUE KEY (cust_id,batch_id)
) ENGINE=InnoDB;

Let's create the customer, batch_sequence and batches tables

mysql> DROP DATABASE IF EXISTS ganeshbora;
Query OK, 3 rows affected (0.40 sec)

mysql> CREATE DATABASE ganeshbora;
Query OK, 1 row affected (0.01 sec)

mysql> USE ganeshbora
Database changed
mysql> CREATE TABLE customer
    -> (
    ->     cust_id INT NOT NULL AUTO_INCREMENT,
    ->     firstname VARCHAR(20),
    ->     lastname  VARCHAR(20),
    ->     address   VARCHAR(40),
    ->     PRIMARY KEY (cust_id)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.30 sec)

mysql> CREATE TABLE batch_sequence
    -> (
    ->     batch_id INT NOT NULL AUTO_INCREMENT,
    ->     PRIMARY KEY (batch_id)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.08 sec)

mysql> CREATE TABLE batches
    -> (
    ->     batch_id INT NOT NULL,
    ->     cust_id INT NOT NULL,
    ->     PRIMARY KEY (batch_id,cust_id),
    ->     UNIQUE KEY (cust_id,batch_id)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.36 sec)

Let's load the customer table

mysql> INSERT INTO customer (firstname,lastname,address) VALUES
    -> ('william','Faulkner','UK'), ('suzy','nicholus','US'),
    -> ('ganesh','bora','india'), ('mahadev','patil','india'),
    -> ('prasad','k','UK'), ('sandeep','g','US'),
    -> ('prajakta','J','india'), ('Nimish','G','india');
Query OK, 8 rows affected (0.04 sec)
Records: 8  Duplicates: 0  Warnings: 0

Let's load the first batch

mysql> LOCK TABLES batch_sequence WRITE;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO batch_sequence VALUES ();
Query OK, 1 row affected (0.00 sec)

mysql> SET @next_batch_id = LAST_INSERT_ID();
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE batch_sequence ENGINE=MyISAM;
Query OK, 1 row affected (0.15 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO batches (batch_id,cust_id) VALUES
    -> (@next_batch_id,1),(@next_batch_id,2),(@next_batch_id,3),(@next_batch_id,4);
Query OK, 4 rows affected (0.05 sec)
Records: 4  Duplicates: 0  Warnings: 0

Let's load the second batch

mysql> LOCK TABLES batch_sequence WRITE;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO batch_sequence VALUES ();
Query OK, 1 row affected (0.00 sec)

mysql> SET @next_batch_id = LAST_INSERT_ID();
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE batch_sequence ENGINE=MyISAM;
Query OK, 2 rows affected (0.10 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO batches (batch_id,cust_id) VALUES
    -> (@next_batch_id,5),(@next_batch_id,6),(@next_batch_id,7),(@next_batch_id,8);
Query OK, 4 rows affected (0.05 sec)
Records: 4  Duplicates: 0  Warnings: 0

Let's see what these table contain

mysql> SELECT * FROM customer;
+---------+-----------+----------+---------+
| cust_id | firstname | lastname | address |
+---------+-----------+----------+---------+
|       1 | william   | Faulkner | UK      |
|       2 | suzy      | nicholus | US      |
|       3 | ganesh    | bora     | india   |
|       4 | mahadev   | patil    | india   |
|       5 | prasad    | k        | UK      |
|       6 | sandeep   | g        | US      |
|       7 | prajakta  | J        | india   |
|       8 | Nimish    | G        | india   |
+---------+-----------+----------+---------+
8 rows in set (0.00 sec)

mysql> SELECT * FROM batches;
+----------+---------+
| batch_id | cust_id |
+----------+---------+
|        1 |       1 |
|        1 |       2 |
|        1 |       3 |
|        1 |       4 |
|        2 |       5 |
|        2 |       6 |
|        2 |       7 |
|        2 |       8 |
+----------+---------+
8 rows in set (0.00 sec)

Let's join them to produce your desired output

mysql> SELECT B.*,A.batch_id FROM batches A INNER JOIN customer B USING (cust_id);
+---------+-----------+----------+---------+----------+
| cust_id | firstname | lastname | address | batch_id |
+---------+-----------+----------+---------+----------+
|       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 |
+---------+-----------+----------+---------+----------+
8 rows in set (0.00 sec)

Let's copy batch 1 into a new batch

mysql> LOCK TABLES batch_sequence WRITE;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO batch_sequence VALUES ();
Query OK, 1 row affected (0.00 sec)

mysql> SET @next_batch_id = LAST_INSERT_ID();
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE batch_sequence ENGINE=MyISAM;
Query OK, 3 rows affected (0.12 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO batches (batch_id,cust_id)
    -> SELECT @next_batch_id,cust_id FROM batches WHERE batch_id = 1;
Query OK, 4 rows affected (0.05 sec)
Records: 4  Duplicates: 0  Warnings: 0

Let's join them again to produce your desired output

mysql> SELECT B.*,A.batch_id FROM batches A INNER JOIN customer B USING (cust_id);
+---------+-----------+----------+---------+----------+
| cust_id | firstname | lastname | address | batch_id |
+---------+-----------+----------+---------+----------+
|       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 |
|       1 | william   | Faulkner | UK      |        3 |
|       2 | suzy      | nicholus | US      |        3 |
|       3 | ganesh    | bora     | india   |        3 |
|       4 | mahadev   | patil    | india   |        3 |
+---------+-----------+----------+---------+----------+
12 rows in set (0.00 sec)

mysql>

The only thing my answer does differently from what you want is generate batch numbers sequentially by 1.

Please note that I made the a third table called batch_sequence. The purpose of this table is just to generate batch ids. I treat the batch_sequence table like and Oracle or PostgreSQL Sequence Object. I prefer making this table MyISAM to ensure a full table lock when creating a new sequence number.

Please also note that LAST_INSERT_ID() is used to retrieve whatever the next sequence number is after an INSERT into the batch_sequence table.

What are the Advantages ?

• You do not need to duplicate names and addresses
• Represent batches with smaller amount of data
• Guarantee uniqueness of batch ids by serialization
• Multiple processes can add batches without batch_id collision

Conclusion

If you decide to go with this, the stored procedure should be using this

LOCK TABLES batch_sequence WRITE;
INSERT INTO batch_sequence VALUES ();
SET @next_batch_id = LAST_INSERT_ID();
ALTER TABLE batch_sequence ENGINE=MyISAM;
UNLOCK TABLES;
INSERT INTO batches (batch_id,cust_id)
SELECT @next_batch_id,cust_id FROM batches WHERE batch_id = (whatever batch id);

If you want to cut-and-paste all the sample code above, here it is

DROP DATABASE IF EXISTS ganeshbora;
CREATE DATABASE ganeshbora;
USE ganeshbora
CREATE TABLE customer
(
    cust_id INT NOT NULL AUTO_INCREMENT,
    firstname VARCHAR(20),
    lastname  VARCHAR(20),
    address   VARCHAR(40),
    PRIMARY KEY (cust_id)
) ENGINE=InnoDB;
CREATE TABLE batch_sequence
(
    batch_id INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (batch_id)
) ENGINE=MyISAM;
CREATE TABLE batches
(
    batch_id INT NOT NULL,
    cust_id INT NOT NULL,
    PRIMARY KEY (batch_id,cust_id),
    UNIQUE KEY (cust_id,batch_id)
) ENGINE=InnoDB;
INSERT INTO customer (firstname,lastname,address) VALUES
('william','Faulkner','UK'), ('suzy','nicholus','US'),
('ganesh','bora','india'), ('mahadev','patil','india'),
('prasad','k','UK'), ('sandeep','g','US'),
('prajakta','J','india'), ('Nimish','G','india');
LOCK TABLES batch_sequence WRITE;
INSERT INTO batch_sequence VALUES ();
SET @next_batch_id = LAST_INSERT_ID();
ALTER TABLE batch_sequence ENGINE=MyISAM;
UNLOCK TABLES;
INSERT INTO batches (batch_id,cust_id) VALUES
(@next_batch_id,1),(@next_batch_id,2),(@next_batch_id,3),(@next_batch_id,4);
LOCK TABLES batch_sequence WRITE;
INSERT INTO batch_sequence VALUES ();
SET @next_batch_id = LAST_INSERT_ID();
ALTER TABLE batch_sequence ENGINE=MyISAM;
UNLOCK TABLES;
INSERT INTO batches (batch_id,cust_id) VALUES
(@next_batch_id,5),(@next_batch_id,6),(@next_batch_id,7),(@next_batch_id,8);
SELECT * FROM customer;
SELECT * FROM batches;
SELECT B.*,A.batch_id FROM batches A INNER JOIN customer B USING (cust_id);
LOCK TABLES batch_sequence WRITE;
INSERT INTO batch_sequence VALUES ();
SET @next_batch_id = LAST_INSERT_ID();
ALTER TABLE batch_sequence ENGINE=MyISAM;
UNLOCK TABLES;
INSERT INTO batches (batch_id,cust_id)
SELECT @next_batch_id,cust_id FROM batches WHERE batch_id = 1;
SELECT B.*,A.batch_id FROM batches A INNER JOIN customer B USING (cust_id);