Using Json for Table Data Movement in SQL Server
Have you ever wanted to copy lots of tables from one SQL Server database to another, but found the Import/Export/Scripting options lacking? Depending on the number of tables you need to move, this can be a time consuming, error-prone, and a generally inflexible process.
What if you could just:
Wrap-up all/some/or most tables from your ‘source’ database into a single json file
Insert (import) this single file into a ‘destination’ database
Replace tables in the ‘destination’ database based on the imported json contents?
Enter: Json Table Transfer Solution (JTTS) for SQL Server 2016 (or later)
This ‘JTTS’ capability consists of 2 stored procedures and 1 SSIS Package; all downloadable here Note: this ‘JTTS’ solution is a small subset of our larger BimlSnap product – so you get other goodies with this link. BTW, our ‘JTTS’ feature is independent of all our ‘Biml’ centric features.
Full Disclosure:
This Json based approach has not been tested on all table/data types
May not be practical for larger tables
May be the easiest way on the planet to move lots of smaller tables between SQL Server databases
From 10,000 feet…
The diagram below illustrated the process flow for ‘JTTS’ data movement between 2 SQL Servers:
Minimum Requirements
SQL Server 2016+ ‘Source’ Database Prerequisites:
Stored Procedure: [jtts].[Json Table Export]
Table: [jtts].[json_repository]
SQL Server 2016+ ‘Destination’ Database Prerequisites:
Stored Procedure: [jtts].[Json Table Import]
Table: [jtts].[json_repository]
SQL Server Data Tools (SSDT) to run the SSIS package:
Transfer Json Repository Row
Step by Step
(1) In SSMS: Run the stored procedure: [jtts].[Json Table Export] from the ‘source’ database (see the stored procedure for parameter definitions and examples):
EXEC [jtts].[Json Table Export] ‘Test’, ‘all’, ”, ‘Y’, ‘N’
(2) In SSDT: Open the project shown below, and set the Project Parameters to point to the right ‘source’, and ‘destination’ databases, as well as the required ‘label’. Once configured, execute the ‘Run All – Transfer Json Repository Row‘ package
(3) In SSMS: Run the stored procedure: [jtts].[Json Table Import] from the ‘destination’ database (see the stored procedure for parameter definitions and examples):
EXEC [jtts].[Json Table Import] ”, ‘Y’, ‘N’, ‘N’, ”, ‘N’, ‘Test’, ‘SRV1’, ‘bimlsnap_v2’
Possibilities to Consider when using this ‘JTTS’ Repository
Multiple ‘point-in-time’ versions of database ‘table data’ could be captured and restored as needed
Json flat files can be created from a single Json (varbinary) column, and then transferred via e-mail, SFTP, or similar
This ‘JTTS’ solution is compatible with SQL Server’s ‘FileTable’ storage feature (see the stored procedure’s usage details for examples)
The JTTS format stores both table data and the exported table definitions
Front-end Option
As a part of the aforementioned download, you get a front-end program for BimlSnap. Using this front-end, simply choose the ‘Import/Export’ menu item to access the same features discussed above:
