INSERT multiple rows from SELECT returns error "This is not permitted" [closed]

Question

Closed. This question is not reproducible or was caused by typos. It is not currently accepting answers.

This question was caused by a typo or a problem that can no longer be reproduced. While similar questions may be on-topic here, this one was resolved in a way less likely to help future readers.

Closed 8 months ago.

Improve this question

I have two tables of same structure and want to insert rows from tableA into tableB.

The following example works fine:

INSERT INTO tableA (uniqueColA, colB, colC)
    SELECT uniqueColA, colB, colC FROM tableB 
    WHERE uniqueColA = 1

However, if the SELECT returns more than one row, like

INSERT INTO tableA (uniqueColA, colB, colC)
    SELECT uniqueColA, colB, colC FROM tableB 
    WHERE uniqueColA IN (1,2)

SQL Server returns the error

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Why that, and how can I solve this?

EDIT: The parentheses around the columns of the SELECT were a typo. Wether there are parentheses around the entire SELECT...WHERE... does not matter, it throws the same error.

You are misusing parenthesis. (SELECT (uniqueColA, colB, colC) FROM ... <> SELECT (uniqueColA, colB, colC) .FROM... <> SELECT uniqueColA, colB, colC FROM.... The only places you should have parenthesis are in your INSERT and IN clauses. — Thom A
– Thom A ♦, Commented Mar 21 at 10:37
Do you perhaps have a faulty trigger in your table? Add full query and the full error message — siggemannen
– siggemannen, Commented Mar 21 at 11:54
I see you fixed the code in your question. As @siggemannen suggested, the error is likely due to a trigger on tableA which is not properly written to handle multiple rows. Remember that triggers fire once per statement rather than per row so the virtual inserted table in the trigger may contain multiple rows. — Dan Guzman
– Dan Guzman, Commented Mar 21 at 14:44
You need to provide minimal reproducible example here. Most likely, as mentioned, there's a poorly written trigger, so you need to provide a copy of the trigger's definition. — Thom A
– Thom A ♦, Commented Apr 11 at 9:12

Harsh Varde · Accepted Answer · 2025-03-21 10:49:40Z

1

No need to wrap select statement in parentheses.

You need to query like this and it will work:

INSERT INTO tableA (uniqueColA, colB, colC)
SELECT uniqueColA, colB, colC 
FROM tableB 
WHERE uniqueColA IN (1,2)

answered Mar 21 at 10:49

Harsh Varde

3035 silver badges10 bronze badges

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

2 Comments

sweber Mar 21 at 11:13

Actually, it does not. I had some extra parentheses in my question, my original query looks like yours - yet, SQL Server says the SELECT returns more than one result...

siggemannen Mar 21 at 11:36

Put your real query in the question, this should work, so i'm guessing your actual query isn't exactly like that

Collectives™ on Stack Overflow

INSERT multiple rows from SELECT returns error "This is not permitted" [closed]

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related