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

I don't get the question. What's stopping you from just making another column and keeping the value in it the same in all 10 inserts? — sachleen
– sachleen, Commented Apr 22, 2013 at 2:14
Are you saying you want to add a second column, for example: secondaryId, and have this be the same for all records inserted? or you want the primaryId to be the same (which is not possible). — SOfanatic
– SOfanatic, Commented Apr 22, 2013 at 2:15
Why would you make the column auto_increment if you don't want it to auto increment? — Explosion Pills
– Explosion Pills, Commented Apr 22, 2013 at 2:17
@ExplosionPills primaryId is auto increment as each row/item needs its' own id. I would like a secondaryId that is the same for all rows inserted during one query. — Alex
– Alex, Commented Apr 22, 2013 at 2:31

John Woo · Accepted Answer · 2013-04-22 02:32:36Z

10

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

edited Apr 22, 2013 at 2:32

answered Apr 22, 2013 at 2:20

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Insert multiple rows with same unique ID

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related