T-SQL Recursive CTE not completing at last record

Question

T-SQL Recursive CTE not completing at last record

SSChasing Mays

Points: 604

March 31, 2023 at 10:38 am

I want to be able to recursively go through the contents of a temp table without using a loop and complete at the last record (ID = 4)

and perform a set of statements

my data looks like this

DROP TABLE IF EXISTS [#ChannelInfo]

CREATE TABLE [#ChannelInfo](
[ID] [bigint] NULL,
[SerialNumber] [nvarchar](60) NULL,
[channelid] [int] NULL
) ON [PRIMARY]
GO

INSERT INTO [#ChannelInfo] ([ID], [SerialNumber],[channelid])
SELECT 1 AS ID, 939029 AS [SerialNumber], 12 as ChannelID
UNION ALL
SELECT 2 AS ID, 939029 AS [SerialNumber], 13 as ChannelID
UNION ALL
SELECT 3 AS ID, 939029 AS [SerialNumber], 14 as ChannelID
UNION ALL
SELECT 4 AS ID, 939029 AS [SerialNumber], 15 as ChannelID

i then tried the following statement but the recursion does not terminate at ID = 4

;with cte as
(
select * from [#ChannelInfo]
union all
select t.* from cte
inner join [#ChannelInfo] t on cte.id = t.id + 1
)
select * from cte a

What am I doing wrong here?

This topic was modified 1 year, 7 months ago by Weegee71.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Not sure what you mean by 'Complete at the last record'. What results are you hoping to see?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Weegee71 SSChasing Mays Points: 604 More actions · Answer 2

Phil Parkin wrote:

Not sure what you mean by 'Complete at the last record'. What results are you hoping to see?

I want to get to the last record

but i thinik ive found the solution

select * from [#ChannelInfo] where ID = 1

union all

select t.* from cte

inner join [#ChannelInfo] t on t.id = cte.id + 1

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

Weegee71 wrote:

Phil Parkin wrote:
Not sure what you mean by 'Complete at the last record'. What results are you hoping to see?
I want to get to the last record
but i thinik ive found the solution
select * from [#ChannelInfo] where ID = 1 union all select t.* from cte inner join [#ChannelInfo] t on t.id = cte.id + 1

This gives the same result as

SELECT *
FROM #ChannelInfo ci;

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Phil Parkin SSC Guru Points: 246960 More actions · Answer 4

'Get to the last record' still makes no sense to me. Once again, I suggest you provide the output you are hoping for.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Weegee71 SSChasing Mays Points: 604 More actions · Answer 5

Ok im going to expand on this.

Ive got to loop through this table and build set of Merge statements.

Currently I am using Loop based logic to extract and execute a set of Merge statements

But I want to avoid using loop based logic and do this as 1 statement and thought that using a recursive CTE

here is more detail

CREATE TABLE [#DeviceChannel](
[SerialNumber] [nvarchar](255) NOT NULL,
[ChannelId] [int] NOT NULL,
[DeviceChannelID] [uniqueidentifier] NOT NULL,
[ChannelReadingTableName] [nvarchar](4000) NULL,
[MainLevelDataTableName] [nvarchar](4000) NULL,
[TableRecordCountsTableName] [nvarchar](4000) NULL
) ON [PRIMARY]
GO

INSERT [#DeviceChannel] ([SerialNumber], [ChannelId], [DeviceChannelID], [ChannelReadingTableName], [MainLevelDataTableName], [TableRecordCountsTableName]) VALUES (N'939029', 12, N'92af1762-03b6-4918-9eec-157a7569e950', N'[ChannelReading_939029_12_92AF1762-03B6-4918-9EEC-157A7569E950]', N'[ChannelReading_939029_12_92AF1762-03B6-4918-9EEC-157A7569E950_MainLevelData]', N'[ChannelReading_939029_12_92AF1762-03B6-4918-9EEC-157A7569E950_MainLevelData_TableRecordCounts]')
GO
INSERT [#DeviceChannel] ([SerialNumber], [ChannelId], [DeviceChannelID], [ChannelReadingTableName], [MainLevelDataTableName], [TableRecordCountsTableName]) VALUES (N'939029', 13, N'aeb97900-e840-4143-bdbd-364d69838191', N'[ChannelReading_939029_13_AEB97900-E840-4143-BDBD-364D69838191]', N'[ChannelReading_939029_13_AEB97900-E840-4143-BDBD-364D69838191_MainLevelData]', N'[ChannelReading_939029_13_AEB97900-E840-4143-BDBD-364D69838191_MainLevelData_TableRecordCounts]')
GO
INSERT [#DeviceChannel] ([SerialNumber], [ChannelId], [DeviceChannelID], [ChannelReadingTableName], [MainLevelDataTableName], [TableRecordCountsTableName]) VALUES (N'939029', 14, N'108c449f-8808-4f41-b41e-6bee2d1a4e3a', N'[ChannelReading_939029_14_108C449F-8808-4F41-B41E-6BEE2D1A4E3A]', N'[ChannelReading_939029_14_108C449F-8808-4F41-B41E-6BEE2D1A4E3A_MainLevelData]', N'[ChannelReading_939029_14_108C449F-8808-4F41-B41E-6BEE2D1A4E3A_MainLevelData_TableRecordCounts]')
GO
INSERT [#DeviceChannel] ([SerialNumber], [ChannelId], [DeviceChannelID], [ChannelReadingTableName], [MainLevelDataTableName], [TableRecordCountsTableName]) VALUES (N'939029', 15, N'27c51350-c2ba-4c67-87f7-f91f56093036', N'[ChannelReading_939029_15_27C51350-C2BA-4C67-87F7-F91F56093036]', N'[ChannelReading_939029_15_27C51350-C2BA-4C67-87F7-F91F56093036_MainLevelData]', N'[ChannelReading_939029_15_27C51350-C2BA-4C67-87F7-F91F56093036_MainLevelData_TableRecordCounts]')
GO

Now when i run the following

;with cte as

(

select * from [#ChannelInfo] where ID = 1

union all

select t.* from cte

inner join [#ChannelInfo] t on t.id = cte.id + 1

)

select 'MERGE ' + dc.ChannelReadingTableName + ' USING ' + DC.[MainLevelDataTableName] from cte

join

[#DeviceChannel] dc on cte.channelid = dc.ChannelId

I get the following(not complete..just code snippet)

MERGE [ChannelReading_939029_12_92AF1762-03B6-4918-9EEC-157A7569E950] USING [ChannelReading_939029_12_92AF1762-03B6-4918-9EEC-157A7569E950_MainLevelData]

MERGE [ChannelReading_939029_13_AEB97900-E840-4143-BDBD-364D69838191] USING [ChannelReading_939029_13_AEB97900-E840-4143-BDBD-364D69838191_MainLevelData]

MERGE [ChannelReading_939029_14_108C449F-8808-4F41-B41E-6BEE2D1A4E3A] USING [ChannelReading_939029_14_108C449F-8808-4F41-B41E-6BEE2D1A4E3A_MainLevelData]

MERGE [ChannelReading_939029_15_27C51350-C2BA-4C67-87F7-F91F56093036] USING [ChannelReading_939029_15_27C51350-C2BA-4C67-87F7-F91F56093036_MainLevelData]

Phil Parkin SSC Guru Points: 246960 More actions · Answer 6

The output you get from your query is the same as

SELECT 'MERGE ' + dc.ChannelReadingTableName + ' USING ' + dc.MainLevelDataTableName
FROM #DeviceChannel dc;

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 7

I think you might want this for a recursive CTE:

;with cte as
(
    select * 
      from [#ChannelInfo] 
     where id = 1  -- Anchor part of CTE
    union all
    select t.* 
      from cte
     inner join [#ChannelInfo] t 
             on cte.id = t.id - 1
)
select * 
  from cte a

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 8

I'm curious... why do you think you need any form of looping for this?

SELECT CONCAT('MERGE ',ChannelReadingTableName,' USING ',MainLevelDataTableName)
  FROM #DeviceChannel

And, be aware... I used the table name you provided for test data, which doesn't match the table name you used in your example code.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)