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
This is the look up table (ChannelInfo) which use [ChannelID]
I then use channelID to locate and filter data in this table [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)
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.ChannelIDSQL 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.ChannelIDHi 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
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply