tshad (7/31/2014)
I am trying to add multiple records to my table (insert/select).
INSERT INTO Users
( User_id ,
Name
)
SELECT ( SELECT MAX(User_id) + 1
FROM Users
) ,
Name
But I get the error:
Violation of PRIMARY KEY constraint 'PK_Users'. Cannot insert duplicate key in object 'dbo.Users'.
But I am using the max User_id + 1, so it can't be duplicate
This would insert about 20 records.
Why the error?
Thanks,
Tom
Try something like (untested but certain it works 😉
😎
INSERT INTO Users
( User_id ,
Name
)
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) + ( SELECT MAX(User_id)
FROM Users
) ,
Name