Duplicate table data X times with unique primary key for testing-CodePudding

I want to test some code but for that I need lots of data. my current table has not enough data, I want to duplicate the data but have unique primary key.

I can create it by using two table but was wondering if there is way to do it without creating two table.

CodePudding user response：

You can use cross joins like this:

--MSSQL
SELECT * FROM 
TABLE AS A ,TABLE AS B,TABLE AS C,
TABLE AS D

Primary key shouldn't be a problem for this query

CodePudding user response：

With MySQL, as long as the PRIMARY KEY field is AUTO_INCREMENT, One (or more) INSERT INTO SELECT will copy the table rows back into itself.

For example:

INSERT INTO employees (name, department_id) 
  SELECT name, department_id FROM employees;

Given:

CREATE TABLE employees 
  (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(32), department_id INT);
INSERT INTO employees 
  (id, name, department_id) 
  VALUES (1, "Karen", 0), (2, "Joe", 1), (3, "Fred", 2), (4, "Sally", 2);

/*
id  name    department_id
1   Karen   0
2   Joe     1
3   Fred    2
4   Sally   2
*/

INSERT INTO employees (name, department_id) 
  SELECT name, department_id FROM employees;

...results in:

/*
id  name    department_id
1   Karen   0
2   Joe     1
3   Fred    2
4   Sally   2
5   Karen   0
6   Joe     1
7   Fred    2
8   Sally   2
*/

Try it here: https://onecompiler.com/mysql/3y759u6ph

This can be incorporated into a procedure:

DELIMITER $$

CREATE PROCEDURE copyEmployees(ii INT)

BEGIN

  DECLARE counter INT DEFAULT 1;

  WHILE counter <= II DO
    INSERT INTO employees (name, department_id)
      SELECT name, department_id FROM employees;
    SET counter = counter   1;
  END WHILE;
    
END$$

DELIMITER ;

CALL copyEmployees(3);
SELECT * FROM employees;

Try it here: https://onecompiler.com/mysql/3y759edxd