Static data formats
Scripting data as INSERT statements is pretty common and technically data is being versioned. However, it is very difficult to understand the change, because column names (that are scripted into one line) and values (that are scripted into another line) do not always match vertically as value line could contain much wider values than column names.
It is even more difficult to understand changes if the database is normalized and more related tables are involved.
DB Version Control allows scripting data into XML/JSON files. For example, a single SalesLT.ProductModel table could be scripted like this:
In addition, XML/JSON files can contain multiple table data by including Reference [R] table data (for columns with foreign key relations) or Child [C] table data (for other tables that have foreign key relations to the selected table).
Some Child [C] tables may have multiple relations to the selected table - therefore you have to choose foreign key columns as well for proper hierarchy view.
For example, SalesLT.ProductModel table with additional related tables:
- SalesLT.Product child [C] table
- SalesLT.ProductCategory reference [R] table
could be scripted like this:
To handle larger files (with multiple related tables), XML/JSON files can be split into multiple files by a column's value (for example, CategoryID).