JSON-SQL-CLI

A command line interface to easier maintain your SQL merge scripts.

Installation

npm install -g json-sql-cli

Usage

Create a json file with the following demo content:

{ 
  "table": "core.Table",
  "key": "GenderId",
  "columns": [
    "GenderId",
    "Gender"
  ],
  "values": [
    {
      "genderId": 1,
      "gender": "männlich"
    },
    {
      "genderId": 2,
      "gender": "weiblich"
    }
  ]
}

Call json-sql <PATH_TO_JSON> <PATH_TO_OUTPUT_FILE>

The following output file should have been generated:

SET IDENTITY_INSERT core.Table ON;
GO

MERGE INTO core.Table AS Target
USING (VALUES 
  (1, N'männlich'),
  (2, N'weiblich')
) AS Source (
  [GenderId],
			[Gender]
)
ON Target.GenderId = Source.GenderId
-- Update matched rows
WHEN MATCHED THEN
UPDATE SET
  Gender = Source.Gender
-- Insert new rows
WHEN NOT MATCHED BY TARGET THEN
INSERT(
  [GenderId],
			[Gender]
)
VALUES (
  [GenderId],
			[Gender]
)
-- delete unmatched rows
WHEN NOT MATCHED BY SOURCE AND Target.GenderId < 1000
THEN DELETE
OUTPUT $action, Inserted.*, Deleted.*;
GO

SET IDENTITY_INSERT core.Table OFF;
GO