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:

  1. Wrap-up all/some/or most tables from your ‘source’ database into a single json file
  2. Insert (import) this single file into a ‘destination’ database
  3. 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

SSIS Project
Parameter Settings

(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: