INSERT with SELECT returning multiple values throws error "subquery returned more than 1 value"

Question

Trying to insert multiple values in a table (as many as ID's from the SELECT result), but the select Id query returns more than 1 value (expected), hence the error.

INSERT INTO table1 (UserId, Date, ...)
VALUES
((SELECT Id from table2 WHERE ClientId = 26), GETDATE(), ...)

what would be the best approach for this?

WAMLeslie · Accepted Answer · 2021-05-05 15:01:07Z

1

I'm not sure how your columns line up. It looks like GETDATE() is inserting into Name. But if that's what you need, what about this? You could just do an INSERT SELECT without the VALUES.

INSERT INTO table1 (UserId, Name, ...)
SELECT Id, GETDATE(), ...
from table2 
WHERE ClientId = 26

answered May 5, 2021 at 15:01

WAMLeslie

1,2711 gold badge7 silver badges15 bronze badges

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

2 Comments

hikizume Over a year ago

I've edited the example but yeah, this did the trick. Will marked as accepted once it lets me. thanks!

WAMLeslie Over a year ago

Glad to hear it.

Collectives™ on Stack Overflow

INSERT with SELECT returning multiple values throws error "subquery returned more than 1 value"

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related