target table has more columns than source and different names how to map it
here is the mapping for source and target table
Amount-Amount_new, category-category_new, "price"-"price","totalprice"-"totalprice1", Result-"totalprice2" ....... reaminginare extra columns in target table ,"totalprice3","totalprice4" and so on
You can use INSERT...SELECT
to map the json fields to target table columns with different names. Any columns not mapped to the target table will contain NULL
.
The below example is based on your example json (with missing ending "}" added). I used data type varchar(100) for illustration but you should change the data types to match the actual target column data types.
If the Products json array contains more than one entry, each row all will have the same value in the result column because that json field is outside the array.
DECLARE @json nvarchar(MAX) = N'
{
"Generic": {
"id": "33",
"Products": [
{
"Code": "111",
"Amount": 1.0,
"category": "33",
"price": 11,
"totalprice": 233
}
],
"Result": "test"
},
"Notification": {
"Environment": "local",
"Instance": "local",
"Time": "00"}
}
';
DROP TABLE IF EXISTS #TargetTable;
CREATE TABLE #TargetTable (
Codenew varchar(100)
, Amount_new varchar(100)
, category_new varchar(100)
, price varchar(100)
, totalprice1 varchar(100)
, totalprice2 varchar(100)
, totalprice3 varchar(100)
, totalprice4 varchar(100)
);
INSERT INTO #TargetTable(
Codenew
, Amount_new
, category_new
, price
, totalprice1
, totalprice2
)
SELECT
Code AS Codenew
, Amount AS Amount_new
, category AS category_new
, price AS price_new
, totalprice AS totalprice1
, result AS totalprice2
FROM OPENJSON(@json)
WITH (
Products nvarchar(MAX) '$.Generic.Products' AS JSON
, result varchar(100) '$.Generic.Result'
)
CROSS APPLY OPENJSON(Products)
WITH (
Code varchar(100)
, Amount varchar(100)
, category varchar(100)
, price varchar(100)
, totalprice varchar(100)
);
SELECT *
FROM #TargetTable;
Results:
Codenew | Amount_new | category_new | price | totalprice1 | totalprice2 | totalprice3 | totalprice4 |
---|---|---|---|---|---|---|---|
111 | 1.0 | 33 | 11 | 233 | test | NULL | NULL |