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

  • 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



     

     

     

  • 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) A socialist is someone who will give you the shirt off *someone else's* back.

  • 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, 1 month ago by  Weegee71.

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

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