This is a Rosetta Code post.
Consider the following relational database:
In the context of this database a
tenant can only be one of
App Two or
User has their own unique GUID and the applications have the following GUIDs:
13 333 records in
dbo.item_line have been persisted incorrectly. The column
dbo.item_line.tenant_id has been populated with the GUID for
App One for the period
01 Jan 2018 to
31 Dec 2018 when it should have been populated with the GUID for the
User tenant. This date is stored in
dbo.item_line.created_by_user_id is a mandatory field and will contain valid user GUIDs. Note that not all users will exist in
The cleanup must be idempotent, this means it can be run multiple times without changing the result beyond the initial story/task.
Cleanup the data using a SQL script in a safe manner.
- Identify all records in
dbo.item_linethat need to be updated, print the count.
- Create records in
dbo.tenantthat dont exist when linking to
dbo.item_line.created_by_user_id, print this count too.
dbo.item_line.tenant_idto be the newly created
dbo.tenant.idwhen it did not exist, else update to be
dbo.item_line.created_by_user_id. Print the total count of updates. This should match the first count printed.
Using the supplied data the prints and counts should be:
total dbo.item_line to be updated : 13333
Spin up a MS SQL Instance, you can then connect to it from Microsoft SQL Server Management Studio on
docker run --name=rosetta-df -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=Password123" -p 1500:1433 -d mcr.microsoft.com/mssql/server:2017-latest
001 CREATE DATABASE.sql
This should get the data into a state that matches the counts above.
A set is an exactly defined collection of objects, this can be accomplished by selecting into a #temptable.
Use a STATIC cursor to iterate over the data.
Specifies a temporary named result set, known as a common table expression (CTE).