This project is for an application user that has duplicate accounts in a database.
Use the Correct Account (11111) and Duplicate Account (11112) as the Key_ID's and search the database for tables that have have a value matching the Key_ID entered.
Begin with a Start_Table table UserAccounts that is master table of account IDNUM's in the database. Use foreign keys to find the tables that reference the other tables and columns for the Key_ID. It would probably be select from system objects the Child Table by the Column Name in Parent Column and list the table names. I have a script that does that. You can start with that script and add the rest. You would probably need to get the rows of the child tables and put them in a temp table, variable, or xml data.
Then use those table names to query the child tables and get the records of each child table and save those and display to allow the user Show the results or Add (Insert/or Update) the results to the Correct Account.
Change the record from the Duplicate Account number (i.e. IDNUM 11112) to the Correct Account number (i.e. IDNUM 11111). Then Insert the records to each table with the Correct Account IDNUM (i.e. 11111) and delete the record with the incorrect IDNUM (i.e. 11112) ,
or Update the Duplicate Account IDNUM (i.e 11112) to the Correct Account 11111 for each child table.
If ShowOrAdd = Show
Show the results that list by table the Correct Account rows, and Duplicate Account rows. The Database name, table names, columns and rows may vary.
The column for the Key_ID is IDNUM
Recap
The script most likely needs the start_table , Key_ID column, and Key_ID value.
Correct Account: start_table= UserAccounts , Key_ID column=IDNUM, Key_ID value=11111
Duplicate Account: start_table= UserAccounts , Key_ID column=IDNUM, Key_ID value=11112
The add records process will need to be a transaction begin, commit due to the probability of many referential integrity constraints.
Example
Correct Account IDNUM 11111
Duplicate Account IDNUM 11112
start_table = UserAccounts
UserAccounts Table
IDNUM Name (Table Columns)
11111 Micky
11112 Minnie
Display
table1
Correct Account has Rows
IDNUM,c1,c2,c3, c3,c5,c6,c7 (Table Columns)
11111, 1, 2, 3, 4, 5, a, b
11111, 1, 2, 3, 4, 1, f, b
11111, 1, 2, 4, 4, 5, a, f
Duplicate Account has Rows
IDNUM,c1,c2,c3, c3,c5,c6,c7 (Table Columns)
11112, 4, 5, 6, 7, 6, c, d
11112, 4, 5, 6, 7, 6, c, e
table2
Correct Account has Rows
IDNUM,c1,c2,c3, c3,c5 (Table Columns)
11111, 1, 2, 3, e, f
11111, 1, 1, 3, e, g
Duplicate Account has Rows
IDNUM,c1,c2,c3, c3,c5 (Table Columns)
11112, 4, 5, 6, g, f
11112, 4, 5, 3, g, j
If ShowOrAdd = Add, Add Duplicate Rows for each child table records to Correct IDNUM Account 11111 records.
50 more tables possible
If you don't already have a test database with many tables and foreign keys you can install a database like Northwinds from github, Sql Server 2016, Use only T-SQL if possible. Reply in advance if you need more information to complete this request.
Please reply with:
The time of day that you are available. I am available 7am to 10pm UTC-06:00 Central Time US & Canada. Will you require contact by voice and do you speak English? When you can complete the project.
Confidentiality.
a) No Use. Recipient agrees not to use the Confidential Information in any way.
b) No Disclosure. Recipient agrees to use its best efforts to prevent and protect the Confidential Information, or any part thereof.
c) Protection of Secrecy. Recipient agrees to take all steps reasonably necessary to protect the secrecy of the Confidential Information, and to prevent the Confidential Information from falling into the public domain or into the possession of unauthorized persons.
d) Scope. The scope of Confidentiality is deemed to be in all contracts present past and future with the Parties to this request