T-SQL Stored Procedure to Merge Accounts for User in Database

This project is for an application user that has duplicate accounts in a large enterprise application with thousands of tables.

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 but it uses cursors and is very slow. Prefer not to use cursors. 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 them to allow the user to click a button to copy the records for the Duplicate Account to the Correct Account.

This is for a server admin with only SSMS and no asp.net. Set a variable ShowOrAdd to Show proposed results in SSMS or Add.

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 Records to Correct IDNUM Account 11111

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 or create several tables with foreign keys and test the script, 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

Programmation C# Serveur SQL Microsoft SQL ASP.NET T-SQL (Transact Structures Query Language)

Nº du projet : #34661957

À propos du projet