March 30, 2023 at 4:01 pm
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
March 30, 2023 at 7:16 pm
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
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
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
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