CODEWITHSUNDEEP
mysqlsqlfieldrecord
Max
Max

Reputation: 5063

How to copy multiple records in MySql?

I have a table "tb_products" in the MySql database with a field "ID" as primary key and auto increment; what's the best practice in SQL to select multiple records by ID and copy them in the same table with new incremented IDs?

Upvotes: 2

Views: 8385

Answers (3)

Barmar
Barmar

Reputation: 780798

INSERT INTO tb_products
SELECT NULL id, field1, field2, field3, ...
FROM tb_products
WHERE id IN (id1, id2, id3, ...)

The field list in the SELECT clause should be all fields except id.

Upvotes: 15

araknoid
araknoid

Reputation: 3125

INSERT INTO NewTbl(Field_1,Field_2,...)
SELECT Field_1,Field_2,...
FROM OldTbl;

Upvotes: 1

bizzehdee
bizzehdee

Reputation: 21003

use mysql's insert select http://dev.mysql.com/doc/refman/5.1/en/insert-select.html, and when your selecting from the existing table, you simply omit selecting the ID, and allow the new table to generate the new id's

Upvotes: 0

Related Questions