Learn how to leverage the power of T-SQL to dynamically create and populate SQL tables based on JSON data. This step-by-step guide will walk you through the process of transforming JSON structures into SQL tables, allowing for flexible and efficient data handling within your SQL Server environment.
Prerequisites:
- Basic understanding of SQL Server and Transact-SQL (T-SQL).
- Access to a SQL Server instance for executing the provided script.
- Familiarity with JSON data structures.
Script:
DECLARE @json NVARCHAR(MAX) = '{
"dataModel": [
{
"ItemCode": 67,
"ItemName": "CADBURY OREO PM£1.25",
"ItemSize": "96G",
"Retail": 1.250,
"SameAsDept": true,
"OnlineStatus": false,
"OnlineValueType": null,
"OnlineRetailValue": 0.00,
"DisplayStoreInfo": false,
"ExtraInformation": "Infolink",
"MainBarcode": 7622201726461,
"PM": "Yes",
"RRP": 1.250,
"CostEach": 0.8367,
"Margin": 19.68,
"DeptName": "1003-CONFECTIONERY (24)",
"SubDeptName": "225-CONFEC CHOCOLATE (100)"
},
{
"ItemCode": 68,
"ItemName": "CADBURY WHITE PM£1.25",
"ItemSize": "90G",
"Retail": 1.250,
"SameAsDept": true,
"OnlineStatus": false,
"OnlineValueType": null,
"OnlineRetailValue": 0.00,
"DisplayStoreInfo": false,
"ExtraInformation": "Infolink",
"MainBarcode": 7622201726560,
"PM": "Yes",
"RRP": 1.250,
"CostEach": 0.8513,
"Margin": 18.28,
"DeptName": "1003-CONFECTIONERY (24)",
"SubDeptName": "225-CONFEC CHOCOLATE (100)"
}
]
}';
DECLARE @columns NVARCHAR(MAX) = '';
DECLARE @insertColumns NVARCHAR(MAX) = '';
DECLARE @sql NVARCHAR(MAX);
-- Extract field names for table creation
SELECT @columns = CONCAT(@columns, ',', QUOTENAME([key]), ' NVARCHAR(MAX)')
FROM OPENJSON(@json, '$.dataModel[0]');
SET @columns = STUFF(@columns, 1, 1, ''); -- Remove the leading comma
-- Extract field names for dynamic INSERT
SELECT @insertColumns = CONCAT(@insertColumns, ',', QUOTENAME([key]))
FROM OPENJSON(@json, '$.dataModel[0]');
SET @insertColumns = STUFF(@insertColumns, 1, 1, ''); -- Remove the leading comma
-- Drop and create a temporary table dynamically
SET @sql = 'IF OBJECT_ID(''tempdb..##JsonTable'') IS NOT NULL DROP TABLE ##JsonTable;';
EXEC sp_executesql @sql;
-- Create table dynamically
SET @sql = 'CREATE TABLE ##JsonTable (' + @columns + ');';
EXEC sp_executesql @sql;
-- Insert records dynamically
SET @sql = 'INSERT INTO ##JsonTable (' + @insertColumns + ')
SELECT ' + @insertColumns + '
FROM OPENJSON(@json, ''$.dataModel'')
WITH (' + @columns + ');';
EXEC sp_executesql @sql, N'@json NVARCHAR(MAX)', @json;
-- Select Temp Table
SET @sql = 'SELECT * FROM ##JsonTable;';
EXEC sp_executesql @sql;
Conclusion:
we explored a powerful technique for dynamically creating and populating SQL tables based on JSON data using T-SQL. This approach allows for the seamless integration of JSON data into your SQL Server environment, providing flexibility and adaptability to changing data structures. By leveraging the OPENJSON function and dynamic SQL, you can efficiently handle diverse JSON datasets and streamline your data processing workflows within SQL Server. As always, exercise caution when working with dynamic SQL and ensure proper security measures are in place to protect your database environment.
This script is compatible with SQL Server 2016 and above, as it relies on the OPENJSON function, which was introduced in SQL Server 2016. Ensure that your SQL Server instance meets this version requirement for successful execution of the provided script. If you are using an earlier version of SQL Server, consider upgrading to take advantage of the enhanced features and capabilities introduced in SQL Server 2016 and subsequent versions.
As always, exercise caution when working with dynamic SQL, and implement proper security measures to safeguard your database environment.
0 Comments
if you have any doubts , please let me know