Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

SELECT vs INSERT INTO SELECT different number of rows with recursive cte

I would like to ask for clarification if this behavior is "by design" or is it a bug.
The idea is that recursive CTE returns 6 rows, but when combined with INSERT INTO ... SELECT it has only 4 rows.

Example: https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=43994e3e6fd8c3228b60a3323c0689ef

Usage of MERGE or any blocking operators like `TOP (100) PERCENT`/`ORDER BY 1 OFFSET 0 ROWS` will cause that both SELECT and INSERT INTO SELECT have the same 6 rows.

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=b3fcfcd39dcee90aff68629f894f8c97

Original source: https://stackoverflow.com/questions/50157079/sql-server-cte-loop-insert-all-record-together

2 votes
Sign in
(thinking…)
Sign in with: oidc
Signed in as (Sign out)

We’ll send you updates on this idea

L. Szozda shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

0 comments

Sign in
(thinking…)
Sign in with: oidc
Signed in as (Sign out)
Submitting...

Feedback and Knowledge Base