1-- Table structure
2CREATE TABLE products (
3    id INT AUTO_INCREMENT PRIMARY KEY,
4    name VARCHAR(100),
5    price DECIMAL(10,2),
6    category VARCHAR(50),
7    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
8);
9
10INSERT INTO products (name, price, category) VALUES ('Widget', 29.99, 'Tools');
11
12-- Copy row with id=1, letting MySQL assign a new auto-increment id
13INSERT INTO products (name, price, category)
14SELECT name, price, category
15FROM products
16WHERE id = 1;
17
18-- Result: new row with id=2, same name/price/category as id=1

1-- Duplicate but change the name
2INSERT INTO products (name, price, category)
3SELECT CONCAT(name, ' (Copy)'), price, category
4FROM products
5WHERE id = 1;
6
7-- Duplicate with a new price
8INSERT INTO products (name, price, category)
9SELECT name, price * 0.9, category
10FROM products
11WHERE id = 1;

1-- For tables with many columns, use a temporary table to avoid listing all columns
2CREATE TEMPORARY TABLE tmp_product AS
3SELECT * FROM products WHERE id = 1;
4
5-- Set id to NULL so auto-increment generates a new one
6UPDATE tmp_product SET id = NULL;
7
8-- Or for tables where id cannot be NULL:
9ALTER TABLE tmp_product MODIFY id INT NULL;
10UPDATE tmp_product SET id = NULL;
11
12INSERT INTO products SELECT * FROM products WHERE FALSE;  -- Copy structure
13INSERT INTO products SELECT * FROM tmp_product;
14
15DROP TEMPORARY TABLE tmp_product;

1-- Get column names programmatically (useful in stored procedures)
2SELECT GROUP_CONCAT(COLUMN_NAME)
3FROM INFORMATION_SCHEMA.COLUMNS
4WHERE TABLE_SCHEMA = 'my_database'
5  AND TABLE_NAME = 'products'
6  AND COLUMN_NAME != 'id';
7
8-- Returns: name,price,category,created_at
9-- Use this to build your INSERT...SELECT statement

1CREATE TABLE users (
2    id INT AUTO_INCREMENT PRIMARY KEY,
3    email VARCHAR(255) UNIQUE,
4    name VARCHAR(100),
5    role VARCHAR(50)
6);
7
8-- This fails if email must be unique
9-- INSERT INTO users (email, name, role) SELECT email, name, role FROM users WHERE id = 1;
10-- ERROR: Duplicate entry '[email protected]' for key 'email'
11
12-- Fix: modify the unique column
13INSERT INTO users (email, name, role)
14SELECT CONCAT('copy_', email), name, role
15FROM users
16WHERE id = 1;

1-- Duplicate all products in the 'Tools' category
2INSERT INTO products (name, price, category)
3SELECT CONCAT(name, ' (Archived)'), price, category
4FROM products
5WHERE category = 'Tools';
6
7-- Duplicate rows and change category
8INSERT INTO products (name, price, category)
9SELECT name, price, 'Clearance'
10FROM products
11WHERE price < 10.00;

1import mysql.connector
2
3conn = mysql.connector.connect(host='localhost', database='mydb', user='root', password='pass')
4cursor = conn.cursor()
5
6source_id = 1
7
8# Get column names excluding the primary key
9cursor.execute("""
10    SELECT GROUP_CONCAT(COLUMN_NAME)
11    FROM INFORMATION_SCHEMA.COLUMNS
12    WHERE TABLE_SCHEMA = DATABASE()
13      AND TABLE_NAME = 'products'
14      AND COLUMN_KEY != 'PRI'
15""")
16columns = cursor.fetchone()[0]
17
18# Duplicate the row
19sql = f"INSERT INTO products ({columns}) SELECT {columns} FROM products WHERE id = %s"
20cursor.execute(sql, (source_id,))
21conn.commit()
22
23new_id = cursor.lastrowid
24print(f"Duplicated row {source_id} -> {new_id}")
25cursor.close()
26conn.close()

1DELIMITER //
2CREATE PROCEDURE duplicate_product(IN source_id INT, OUT new_id INT)
3BEGIN
4    INSERT INTO products (name, price, category)
5    SELECT CONCAT(name, ' (Copy)'), price, category
6    FROM products
7    WHERE id = source_id;
8
9    SET new_id = LAST_INSERT_ID();
10END //
11DELIMITER ;
12
13-- Usage
14CALL duplicate_product(1, @new_id);
15SELECT @new_id;