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, 9 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: 246988 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

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

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: 246988 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

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Phil Parkin SSC Guru Points: 246988 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

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

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: 246988 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

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Jonathan AC Roberts SSCoach Points: 18363 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: 1004414 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)