add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE SourceTable
(
ID int,
Column1 nvarchar(max),
Column2 nvarchar(max)
)
CREATE TABLE DestinationTable
(
ID int,
DestinationColumn nvarchar(max)
)



CREATE TABLE Joiningtable
(
TID int
)


INSERT INTO SourceTable (ID, Column1,Column2) Values
(1,'exists','exists'),
(2,'exists',NULL),
(3,NULL,'exists'),
(4,NULL,NULL);

INSERT INTO DestinationTable (ID, DestinationColumn) Values
(1,'{"Attr1":"AAAAAAAAA","Attr2":70,"Attr3":null,"Attr4":false}'),
(2,'{"Attr1":"AAAAAAAAA","Attr2":70,"Attr3":null,"Attr4":false}'),
(3,'{"Attr1":"AAAAAAAAA","Attr2":70,"Attr3":null,"Attr4":false}'),
(4,'{"Attr1":"AAAAAAAAA","Attr2":70,"Attr3":null,"Attr4":false}');

INSERT INTO Joiningtable (TID) Values
(1),
(2),
ID Column1 Column2
1 exists exists
2 exists null
3 null exists
4 null null
ID DestinationColumn
1 {"Attr1":"AAAAAAAAA","Attr2":70,"Attr3":null,"Attr4":false}
2 {"Attr1":"AAAAAAAAA","Attr2":70,"Attr3":null,"Attr4":false}
3 {"Attr1":"AAAAAAAAA","Attr2":70,"Attr3":null,"Attr4":false}
4 {"Attr1":"AAAAAAAAA","Attr2":70,"Attr3":null,"Attr4":false}
TID
1
2
3
4
SELECT
JSON_MODIFY(
JSON_MODIFY(
JSON_MODIFY(
dt.DestinationColumn,
'$.NewAttr',
JSON_QUERY('{}')
),
'$.NewAttr."SourceTable.Column1"',
st.Column1
),
'$.NewAttr."SourceTable.Column2"',
st.Column2
)
FROM dbo.Joiningtable jt
INNER JOIN dbo.DestinationTable dt on dt.ID = jt.TID
INNER JOIN dbo.SourceTable st on jt.TID = st.ID;
(No column name)
{"Attr1":"AAAAAAAAA","Attr2":70,"Attr3":null,"Attr4":false,"NewAttr":{"SourceTable.Column1":"exists","SourceTable.Column2":"exists"}}
{"Attr1":"AAAAAAAAA","Attr2":70,"Attr3":null,"Attr4":false,"NewAttr":{"SourceTable.Column1":"exists"}}
{"Attr1":"AAAAAAAAA","Attr2":70,"Attr3":null,"Attr4":false,"NewAttr":{"SourceTable.Column2":"exists"}}
{"Attr1":"AAAAAAAAA","Attr2":70,"Attr3":null,"Attr4":false,"NewAttr":{}}
SELECT
JSON_MODIFY(dt.DestinationColumn, '$.NewAttr', JSON_QUERY(j.NewAttr))
FROM dbo.Joiningtable jt
INNER JOIN dbo.DestinationTable dt on dt.ID = jt.TID
INNER JOIN dbo.SourceTable st on jt.TID = st.ID
OUTER APPLY (
SELECT
[SourceTable.Column1] = st.Column1,
[SourceTable.Column2] = st.Column2
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
) j(NewAttr);
(No column name)
{"Attr1":"AAAAAAAAA","Attr2":70,"Attr3":null,"Attr4":false,"NewAttr":{"SourceTable":{"Column1":"exists","Column2":"exists"}}}
{"Attr1":"AAAAAAAAA","Attr2":70,"Attr3":null,"Attr4":false,"NewAttr":{"SourceTable":{"Column1":"exists"}}}
{"Attr1":"AAAAAAAAA","Attr2":70,"Attr3":null,"Attr4":false,"NewAttr":{"SourceTable":{"Column2":"exists"}}}
{"Attr1":"AAAAAAAAA","Attr2":70,"Attr3":null,"Attr4":false,"NewAttr":{}}