Use a recursive CTE to produce 1 update statement using dynamic SQL

Question

Post reply

Use a recursive CTE to produce 1 update statement using dynamic SQL

Weegee71

SSChasing Mays

Points: 604

March 30, 2023 at 4:01 pm

#4167189

I want to be able to generate 1 merge statement rather than loop through a lookup table using

a single Set based Recursive CTE statement but im struggling on this.

I have a set of tables which are update by means of a look up table using a merge statement

ChannelInfo

This is the look up table (ChannelInfo) which use [ChannelID]

I then use channelID to locate and filter data in this table [ChannelReadingMain]

ChannelReadingMain

Once data is selected out of ChannelReadingMain, then its Merged into a destination table

which is looked up from this table [DeviceChannel] (via ChannelID)

Devicechannel

So i can loop though and produce a set of merge Statements which are executed in squence .,, for example if i wanted to Merge data for ChannelD = 15...then it would be as follows:

MERGE [ChannelReading_939029_15_27C51350-C2BA-4C67-87F7-F91F56093036] T

USING

(

SELECT

[Si] as SIReading,

[Raw] as RawReading,

[TimeStamp] as ReadingDateTime

FROM

[#ChannelReadingMain]

WHERE

ChannelID = '15'

) S on T.ReadingDateTime = S.ReadingDateTime

WHEN NOT MATCHED BY TARGET

THEN

INSERT (ReadingDateTime,SIReading, RawReading)

VALUES (S.ReadingDateTime,S.SIReading, S.RawReading);

This is build dynamically... but is run as part of a loop and takes time.

I want to convert this to a single Set based update statement using a CTE

But i get the following error

Msg 40515, Level 15, State 1, Line 130

Reference to database and/or server name in 'b.cte2.ChannelReadingTablename' is not supported in this version of SQL Server.

I think this is to do with scope of the Cte in DynamicSQL

How can i utilizie a cte to produce 1 update statement?

The code to recreate this is attached here:

/*****************************************************************************************************************************************************************************************************************************/
/*TABLE CREATION */
/*****************************************************************************************************************************************************************************************************************************/
SET NOCOUNT ON

DROP TABLE IF EXISTS [#ChannelReadingMain]
DROP TABLE IF EXISTS [#DeviceChannel]
DROP TABLE IF EXISTS [#ChannelInfo]

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

CREATE TABLE [#ChannelReadingMain](
[ID] [bigint] NULL,
[SerialNumber] [nvarchar](60) NULL,
[channelid] [int] NULL,
[SI] [decimal](10, 2) NULL,
[raw] [decimal](10, 2) NULL,
[Conversion] [int] NULL,
[timeStamp] [datetime] NULL
) ON [PRIMARY]
GO



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

/*****************************************************************************************************************************************************************************************************************************/
/*POPULATE TABLES */
/*****************************************************************************************************************************************************************************************************************************/

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

-- POPULATE #ChannelReadingMain

INSERT [#ChannelReadingMain] ([ID], [SerialNumber], [channelid], [SI], [raw], [Conversion], [timeStamp]) VALUES (1, N'939029', 12, CAST(0.00 AS Decimal(10, 2)), CAST(0.00 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T13:49:13.000' AS DateTime))
GO
INSERT [#ChannelReadingMain] ([ID], [SerialNumber], [channelid], [SI], [raw], [Conversion], [timeStamp]) VALUES (2, N'939029', 12, CAST(3.12 AS Decimal(10, 2)), CAST(0.00 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T14:08:57.000' AS DateTime))
GO
INSERT [#ChannelReadingMain] ([ID], [SerialNumber], [channelid], [SI], [raw], [Conversion], [timeStamp]) VALUES (3, N'939029', 12, CAST(3.12 AS Decimal(10, 2)), CAST(0.00 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T16:08:57.000' AS DateTime))
GO
INSERT [#ChannelReadingMain] ([ID], [SerialNumber], [channelid], [SI], [raw], [Conversion], [timeStamp]) VALUES (4, N'939029', 12, CAST(3.12 AS Decimal(10, 2)), CAST(0.00 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T18:08:57.000' AS DateTime))
GO

INSERT [#ChannelReadingMain] ([ID], [SerialNumber], [channelid], [SI], [raw], [Conversion], [timeStamp]) VALUES (5, N'939029', 13, CAST(0.00 AS Decimal(10, 2)), CAST(0.00 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T13:49:13.000' AS DateTime))
GO
INSERT [#ChannelReadingMain] ([ID], [SerialNumber], [channelid], [SI], [raw], [Conversion], [timeStamp]) VALUES (6, N'939029', 13, CAST(3.30 AS Decimal(10, 2)), CAST(0.00 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T14:08:57.000' AS DateTime))
GO
INSERT [#ChannelReadingMain] ([ID], [SerialNumber], [channelid], [SI], [raw], [Conversion], [timeStamp]) VALUES (7, N'939029', 13, CAST(3.30 AS Decimal(10, 2)), CAST(0.00 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T16:08:57.000' AS DateTime))
GO
INSERT [#ChannelReadingMain] ([ID], [SerialNumber], [channelid], [SI], [raw], [Conversion], [timeStamp]) VALUES (8, N'939029', 13, CAST(3.30 AS Decimal(10, 2)), CAST(0.00 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T18:08:57.000' AS DateTime))
GO


INSERT [#ChannelReadingMain] ([ID], [SerialNumber], [channelid], [SI], [raw], [Conversion], [timeStamp]) VALUES (9, N'939029', 14, CAST(3.30 AS Decimal(10, 2)), CAST(0.00 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T20:08:57.000' AS DateTime))
GO
INSERT [#ChannelReadingMain] ([ID], [SerialNumber], [channelid], [SI], [raw], [Conversion], [timeStamp]) VALUES (10, N'939029', 14, CAST(3.30 AS Decimal(10, 2)), CAST(0.00 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T22:08:57.000' AS DateTime))
GO
INSERT [#ChannelReadingMain] ([ID], [SerialNumber], [channelid], [SI], [raw], [Conversion], [timeStamp]) VALUES (11, N'939029', 14, CAST(3.30 AS Decimal(10, 2)), CAST(0.00 AS Decimal(10, 2)), 0, CAST(N'2022-05-21T00:08:57.000' AS DateTime))
GO
INSERT [#ChannelReadingMain] ([ID], [SerialNumber], [channelid], [SI], [raw], [Conversion], [timeStamp]) VALUES (12, N'939029', 14, CAST(3.30 AS Decimal(10, 2)), CAST(0.00 AS Decimal(10, 2)), 0, CAST(N'2022-05-21T02:08:57.000' AS DateTime))
GO

INSERT [#ChannelReadingMain] ([ID], [SerialNumber], [channelid], [SI], [raw], [Conversion], [timeStamp]) VALUES (19, N'939029', 15, CAST(3.30 AS Decimal(10, 2)), CAST(0.00 AS Decimal(10, 2)), 0, CAST(N'2022-05-21T04:08:57.000' AS DateTime))
GO
INSERT [#ChannelReadingMain] ([ID], [SerialNumber], [channelid], [SI], [raw], [Conversion], [timeStamp]) VALUES (20, N'939029', 15, CAST(3.30 AS Decimal(10, 2)), CAST(0.00 AS Decimal(10, 2)), 0, CAST(N'2022-05-21T06:08:57.000' AS DateTime))
GO
INSERT [#ChannelReadingMain] ([ID], [SerialNumber], [channelid], [SI], [raw], [Conversion], [timeStamp]) VALUES (21, N'939029', 15, CAST(0.00 AS Decimal(10, 2)), CAST(0.00 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T13:49:13.000' AS DateTime))
GO
INSERT [#ChannelReadingMain] ([ID], [SerialNumber], [channelid], [SI], [raw], [Conversion], [timeStamp]) VALUES (22, N'939029', 15, CAST(39.74 AS Decimal(10, 2)), CAST(0.00 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T13:58:57.000' AS DateTime))
GO
INSERT [#ChannelReadingMain] ([ID], [SerialNumber], [channelid], [SI], [raw], [Conversion], [timeStamp]) VALUES (23, N'939029', 15, CAST(40.45 AS Decimal(10, 2)), CAST(0.00 AS Decimal(10, 2)), 0, CAST(N'2022-05-20T14:08:57.000' AS DateTime))
GO



-- POPULATE #DeviceChannel

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

--select * from [#ChannelInfo]

--SELECT * FROM [#ChannelReadingMain]

--SELECT * FROM [#DeviceChannel]

SET NOCOUNT OFF

DECLARE @SQL    NVARCHAR(4000)

set @SQL = 
'
;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

cte2 as (

   select 
   A.ID,
   dc.* from cte a
   join #DeviceChannel dc on a.channelid = dc.ChannelId
)

select * from cte2
    MERGE b.  cte2.[ChannelReadingTablename] + 
USING
                                (
                                    SELECT 
                                       [Si]as SIReading,
                                       [Raw]as RawReading,
                                       [TimeStamp]as ReadingDateTime
                                   FROM
                                        [#ChannelReadingMain]
                                   WHERE 
                                        ChannelID = cte2.channelid
   ) S  on T.ReadingDateTime = S.ReadingDateTime  
 WHEN NOT MATCHED BY TARGET 
 THEN 
 INSERT (ReadingDateTime,SIReading, RawReading)
 VALUES (S.ReadingDateTime,S.SIReading, S.RawReading); 
from cte2
'
print @sql

EXEC sp_executesql @SQL



--select * from cte2


--;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

--cte2 as (

--   select 
--   A.ID,
--   dc.* from cte a
--   join #DeviceChannel dc on a.channelid = dc.ChannelId
--)

--select ChannelReadingTableName, channelid from cte2

Viewing 3 posts - 1 through 2 (of 2 total)

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

ScottPletcher SSC Guru Points: 101108 More actions · Answer 1

Since you're only doing INSERT and not UPDATE, you don't need MERGE.

I really can't follow the different tables you have since there's only one source table for all the columns you INSERT.

INSERT INTO [ChannelReading_939029_15_27C51350-C2BA-4C67-87F7-F91F56093036] ( ReadingDateTime,SIReading, RawReading )
SELECT  S.[Si] as SIReading, S.[Raw] as RawReading, S.[TimeStamp] as ReadingDateTime
FROM [ChannelReading_939029_15_27C51350-C2BA-4C67-87F7-F91F56093036] T
INNER JOIN [#ChannelReadingMain] S ON T.ReadingDateTime = S.ReadingDateTime
/* I don't see why this is needed, since you don't use any columns from #ChannelInfo in the INSERT */
INNER JOIN [#ChannelInfo] I ON I.ChannelID = T.ChannelID

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Weegee71 SSChasing Mays Points: 604 More actions · Answer 2

ScottPletcher wrote:

Since you're only doing INSERT and not UPDATE, you don't need MERGE.

I really can't follow the different tables you have since there's only one source table for all the columns you INSERT.

INSERT INTO [ChannelReading_939029_15_27C51350-C2BA-4C67-87F7-F91F56093036] ( ReadingDateTime,SIReading, RawReading )
SELECT  S.[Si] as SIReading, S.[Raw] as RawReading, S.[TimeStamp] as ReadingDateTime
FROM [ChannelReading_939029_15_27C51350-C2BA-4C67-87F7-F91F56093036] T
INNER JOIN [#ChannelReadingMain] S ON T.ReadingDateTime = S.ReadingDateTime
/* I don't see why this is needed, since you don't use any columns from #ChannelInfo in the INSERT */INNER JOIN [#ChannelInfo] I ON I.ChannelID = T.ChannelID

Hi Scott

OK that does sound good. But how do i call this statment dynamically and for all channels (12,13,14,15)?

sorry I should have mentoned this in my op ...but the source table is located via a channelID...which in the above case is

15

There are 3 other tables which are specific to a channel ie like this

ChannelReadingTableName

The code has to locate the table specific to a channelID which is this case is 12.. Now the table #DeviceChannel has details of this table...and i can locate by searching on channelID...but this statement is run mutiple times in a loop.

In other words i have to generate a MERGE statement and execute for each channel. Now this was been done in a loop...which was far too long.

i was wondering how a set based approach look and can it be done through a recursive cte which im struggling with

This reply was modified 1 year, 8 months ago by Weegee71.