Static Data With No Primary Key

sburke

I am in the process of evaluating the Redgate Development tools, with an initial focus on Source Control and Deployment processes. We use the Wherescape Red Data Warehousing development tool which creates meta data tables in our SQL Server database, with a small number not including primary keys. I would like to capture these in source control as static tables.

I would like to get some guidance on how best to achieve this. Should I create scripts, or is there another aspect of the Redgate software you would suggest?

James B

Thanks for your post.

I'm afraid I'm not familiar at all with the Wherescape tool you mention, although that's probably not too relevant.

To source control data (with SQL Source Control) the data must have a primary key or a unique index. If neither of those are present, then our data comparison engine doesn't know how to evaluate which records should be compared.

The SQL Data Compare tool allows you to configure a "custom comparison key" where you can manually select one or more columns that, in combination, would uniquely identify rows. Unfortunately this functionality isn't in SQL Source Control as it stands.

SQL Data Compare can compare from a database to a set of scripts, so potentially you could sync the DB to some .sql files after configuring a comparison key and then manually source-control those files using something like Tortoise (if using SVN) for example.

Some links that give more detail:

What is a comparison key?

Selecting a comparison key

meister

Can someone confirm that there is no other solution than the one mentioned before? Thanks in advance.

Andrea_Angella

I confirm that it is not possible to use the static data feature in this scenario.

You can only source-control data in tables with a valid primary key. The primary key is used as the comparison key to identify corresponding rows.

More information here:
https://documentation.red-gate.com/soc6/common-tasks/link-static-data

Regards

way0utwest

To track changes, how would you know which row to change without a PK? If the text is unique, this should just be the PK. If it's not, then with duplicates, there's no good way to determine which rows to update unless we were to look at every field, in which case how can you determine it's an update and not a new row?

A PK is needed. WHERESCAPE may not create one, but likely there is a field that could be used as a PK, or a combination of fields.

wolfixx

You can simply order the data based on the column order
ORDER by 1,2,3,4,5,...

This is not ideal but good enough for source control. This is an approach done by other tools.

Can you please think about this again?