Insert multiple rows with same unique ID

Question

I am inserting multiple rows using one query and, obviously, the ID column auto increments each row. I want to create another ID column and have the ID remain the same for all rows inserted during the query. So if I insert 10 rows during one query, I want all 10 rows to have the id "1". How can this be done? Thanks for any help

Answer 1

If I understood your question correctly, you want to supply an ID for the specific group of INSERT statements.

Assumming you have this schema

CREATE TABLE TableName
(
    RecordID INT AUTO_INCREMENT PRIMARY KEY,
    OtherColumn VARCHAR(25) NOT NULL,
    GroupID INT NOT NULL
)

You can have two statements for this:

1.) Getting the last GroupID and increment it by 1.

SELECT COALESCE(MAX(GroupID), 0) + 1 AS newGroupID FROM TableName

2.) once you have executed it, store the value in a variable. Use this variable for all the insert statement,

$groupID = row['newGroupID'];
$insert1 = "INSERT INTO TableName(OtherColumn, GroupID) VALUES ('a', $groupID)";
$insert2 = "INSERT INTO TableName(OtherColumn, GroupID) VALUES ('b', $groupID)";
$insert3 = "INSERT INTO TableName(OtherColumn, GroupID) VALUES ('c', $groupID)";

UPDATE 1

• SQLFiddle Demo