0

I want to capture and increment variable across multiple select statements:

DECLARE @count INT = 0;

SELECT parent = 0, id = @count++
INTO #tmp1
FROM ...

SELECT parent = id, id = @count++
INTO #tmp2
FROM ...

Obviously ROW_NUMBER() is no good here. How I can achieve this?

Improve this question
2
  • UNION ALL the queries? Commented Oct 10, 2019 at 11:33
  • not good solution because in my case union and recursion in general is impossible or will be too complicated. Commented Oct 10, 2019 at 11:37

1 Answer 1

Reset to default
4

I think you can use variables and row_number() for this:

DECLARE @count INT = 0;

SELECT parent = 0, row_number() over (order by (select null)) as id
INTO #tmp1
FROM ...;

SET @count = @count + @@ROWCOUNT;

SELECT parent = id,
       (@count + row_number() over (order by (select null))) as id
INTO #tmp2
FROM ...;

SET @count = @count + @@ROWCOUNT;
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.