Yosiasz,
i have added your part of query in mine it leads to an error.
error description.
Msg 8155, Level 16, State 2, Line 46
No column name was specified for column 1 of 'src'.
Msg 207, Level 16, State 1, Line 46
Invalid column name 'SearchLogId'.
declare @projectname as nvarchar(max)='DBEU01001',
@dbversion as nvarchar(max)='5d676dc9',
@boxid as nvarchar(max)=null,
@startdate as nvarchar(max)='2020-01-01',
@enddate as nvarchar(max)='2020-01-31'
declare @result TABLE(
RowID int not null primary key identity(1,1),
recordid int,
projectname nvarchar(1000),
apiname nvarchar(100),
device nvarchar(50),
brand nvarchar(max),
model nvarchar(max),
deviceid nvarchar(max),
searchdate datetime ,
feedback nvarchar(10),
details nvarchar(max)
)
declare @minrow int
declare @maxrow int
declare @recid int
declare @json nvarchar(max)
declare @loopcounter int
insert into @result
SELECT SearchLog.SearchLogId,ProjectDetails.ProjectName,'Auto Search' as apiname, SearchLog.Device, SearchLog.Brand, SearchLog.Model, DeviceAuthentication.BoxId, SearchLog.CreatedDate,
'' as feedback,'' as details
FROM SearchLog INNER JOIN
DeviceAuthentication ON SearchLog.FK_DeviceId = DeviceAuthentication.DeviceID INNER JOIN
ProjectInfo ON DeviceAuthentication.FK_ProjectID = ProjectInfo.ProjectInfoId INNER JOIN
ProjectDetails ON ProjectInfo.FK_ProjectId = ProjectDetails.ProjectId
WHERE (SearchLog.Model is NULL) and
(ProjectDetails.ProjectName =@projectname) and (ProjectInfo.EmbeddedDBVersion=@dbversion) and
(convert(date,SearchLog.CreatedDate,105) BETWEEN @startdate AND @enddate) option(recompile)
update tgt
set tgt.details=src.Json
From @result tgt join
(
SearchLog.Device,SearchLog.Brand,SearchLog.Model,SearchLog.VendorId,SearchLog.OSD,SearchLog.Edid
from SearchLog FOR JSON AUTO
) src on tgt.recordid = src.searchLogId
select * from @result