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,'12xx12','0192xx'),
(2,'12xx12',NULL),
(3,NULL,'0192xx'),
(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 | 12xx12 | 0192xx |
| 2 | 12xx12 | null |
| 3 | null | 0192xx |
| 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 |
| scenario | DestinationColumn |
|---|---|
| Both Col 1 and 2 exists | {"Attr1":"AAAAAAAAA","Attr2":70,"Attr3":null,"Attr4":false,"NewAttr":"SourceTable.Column1": "12xx12":SourceTable.Column2": "0192xx"} |
| Only Col 1 exists | {"Attr1":"AAAAAAAAA","Attr2":70,"Attr3":null,"Attr4":false,"NewAttr":"SourceTable.Column1": "12xx12"} |
| Only Col 2 exists | {"Attr1":"AAAAAAAAA","Attr2":70,"Attr3":null,"Attr4":false,"NewAttr":"SourceTable.Column2": "0192xx"} |
| Both empty | {"Attr1":"AAAAAAAAA","Attr2":70,"Attr3":null,"Attr4":false,"NewAttr":{}} |