usp_SQLServerCarpenter_Tools_GenerateTSQLCode_For_Get_Put_APIs

/*
Author:Brahmanand Shukla (SQLServerCarpenter.com)
Date:27-May-2022
Purpose:To generate SQL Code for Get and Put API's. This may be useful if you are developing your own data replication strategy to replicate data across multiple servers.
The procedure returns the following two columns:
* GetSQL: T-SQL Code Read the data in JSON format.
* PutSQL: T-SQL Code to parse the JSON and load in the table.
*** Note: 
1) The name and structure (or the schema) of the table on both Source and Destination DB should be same.
2) text and image type columns can't be replicated through this procedure as JSON doesn't support them.
The procedure accepts one input parameter i.e. @TableName. Procedure runs for one table at a time. The table name needs to be supplied through this parameter.
Example:
EXEC [dbo].[usp_SQLServerCarpenter_Tools_GenerateTSQLCode_For_Get_Put_APIs]
@TableName='Customer_Staging'
*/CREATE OR ALTER PROCEDURE [dbo].[usp_SQLServerCarpenter_Tools_GenerateTSQLCode_For_Get_Put_APIs]
(
@TableNameSYSNAME
)
AS
BEGIN
SET NOCOUNT ON

DECLARE @GetSQLNVARCHAR(MAX)
, @PutSQLNVARCHAR(MAX)
DECLARE @ColumnsNVARCHAR(MAX)
, @ColumnsForSelectGetNVARCHAR(MAX)
, @ColumnsForSelectPutNVARCHAR(MAX)
, @WithClauseNVARCHAR(MAX)
SELECT @Columns=ISNULL(@Columns, '') + ', ' + COALESCE(QUOTENAME(COLUMN_NAME) + CHAR(10) + CHAR(9), ', ') 
, @ColumnsForSelectGet=ISNULL(@ColumnsForSelectGet, '') + ', ' 
+ COALESCE(CASE 
WHEN DATA_TYPE = 'varbinary' 
THEN 'CONVERT(nvarchar(max), ' + QUOTENAME(COLUMN_NAME) + ', 1' + ') AS ' + QUOTENAME(COLUMN_NAME)
WHEN DATA_TYPE = 'binary' 
THEN 'CONVERT(nvarchar(max), ' + QUOTENAME(COLUMN_NAME) + ', 1' + ') AS ' + QUOTENAME(COLUMN_NAME)
ELSE QUOTENAME(COLUMN_NAME)
END
+ CHAR(10) + CHAR(9), ', ') 
, @ColumnsForSelectPut=ISNULL(@ColumnsForSelectPut, '') + ', ' 
+ COALESCE(CASE 
WHEN DATA_TYPE = 'varbinary' 
THEN 'CONVERT(varbinary(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR)  + '), '  + QUOTENAME(COLUMN_NAME) + ', 1' + ')' 
WHEN DATA_TYPE = 'binary' 
THEN 'CONVERT(binary(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR)  + '), ' + QUOTENAME(COLUMN_NAME) + ', 1' + ')' 
ELSE QUOTENAME(COLUMN_NAME)
END
+ CHAR(10) + CHAR(9), ', ') 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
AND DATA_TYPE NOT IN ('text', 'image')
SET @Columns = RIGHT(LEFT(@Columns, (LEN(@Columns) - 2)), (LEN(LEFT(@Columns, (LEN(@Columns) - 2))) - 2))
SET @ColumnsForSelectGet = RIGHT(LEFT(@ColumnsForSelectGet, (LEN(@ColumnsForSelectGet) - 2)), (LEN(LEFT(@ColumnsForSelectGet, (LEN(@ColumnsForSelectGet) - 2))) - 2))
SET @ColumnsForSelectPut = RIGHT(LEFT(@ColumnsForSelectPut, (LEN(@ColumnsForSelectPut) - 2)), (LEN(LEFT(@ColumnsForSelectPut, (LEN(@ColumnsForSelectPut) - 2))) - 2))
SELECT @WithClause=ISNULL(@WithClause, '') + ', ' 
+ COALESCE(QUOTENAME(COLUMN_NAME) + CHAR(9) + CHAR(9) + CHAR(9)
+CASE 
WHEN DATA_TYPE = 'varbinary' 
THEN 'nvarchar' 
WHEN DATA_TYPE = 'binary' 
THEN 'nvarchar'
ELSE QUOTENAME(DATA_TYPE)
END 
+CASE 
WHEN DATA_TYPE IN ('varchar', 'char', 'nchar', 'nvarchar' ) 
THEN
CASE
WHEN (CHARACTER_MAXIMUM_LENGTH = -1)
THEN '(MAX)'
ELSE '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) + ')'
END
WHEN DATA_TYPE IN ('numeric', 'decimal' )
    THEN '(' + CAST( ISNULL(NUMERIC_PRECISION, 0) AS VARCHAR ) + ',' + CAST( ISNULL(NUMERIC_SCALE, 0) AS VARCHAR) + ')'
WHEN DATA_TYPE = 'varbinary' 
THEN '(MAX)' 
WHEN DATA_TYPE = 'binary' 
THEN '(MAX)'
ELSE '' 
END 
+ CHAR(10) + CHAR(9), ', ') 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
AND DATA_TYPE NOT IN ('text', 'image')
SET @WithClause ='WITH' + CHAR(10) + '(' + CHAR(10) + CHAR(9) 
+ RIGHT(LEFT(@WithClause, (LEN(@WithClause) - 2)), (LEN(LEFT(@WithClause, (LEN(@WithClause) - 2))) - 2)) + CHAR(10) 
+ ')'
SET @GetSQL='SELECT ' + @ColumnsForSelectGet + CHAR(10) 
+ 'FROM ' + @TableName + ' WITH(NOLOCK)' + CHAR(10) 
+ 'FOR JSON PATH'
SET @PutSQL='INSERT INTO ' + @TableName + CHAR(10) + ' (' + CHAR(10) + CHAR(9) + @Columns + ' )' + CHAR(10) 
+ 'SELECT ' + @ColumnsForSelectPut + CHAR(10) 
+ 'FROM OPENJSON(@JSON)' + CHAR(10)  
+ @WithClause + ' tbl'
SELECT CAST(@GetSQL AS XML) AS [GetSQL], CAST(@PutSQL AS XML) AS [PutSQL]
END