Post reply

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

  • 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



     

     

     

  • March 30, 2023 at 7:16 pm

    #4167234

    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".

  • March 30, 2023 at 8:48 pm

    #4167255

    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 2 years, 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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy