T-SQL Demostrate methods to loop through query results of table list, select records of tables listed, insert records to other tables.

Closed Posted 1 year ago Paid on delivery
Closed Paid on delivery

T-SQL Script to Merge Accounts for User in Database Multiple Tables. We want a demonstration of methods using temp table, variable, or xml data. Show for each method difference in use of memory for drive space and ram.

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. 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 them to allow the user Show the results or Add the results to the User's records.

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 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 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.

d) Scope. The scope of Confidentiality is deemed to be in all contracts present past and future with the Parties to this request

Microsoft SQL Server T-SQL (Transact Structures Query Language)

Project ID: #34671635

About the project

5 proposals Remote project Active 1 year ago

5 freelancers are bidding on average $84 for this job

bizmanas

Hi I have 15 years experience with sql server database development, mysql database development, performance tuning. I will be able to help you with the project. Please ping me offline to discuss further.

$200 USD in 1 day
(5 Reviews)
3.8
vinothnsm88

I have 9+ years of experience in Microsoft SQL Server as developer, database administrator, ETL (SSIS) developer and Reporting (SSRS) using SQL Server 2016/2014/2012/2008 and also Completed MCSA( Microsoft Certified So More

$55 USD in 2 days
(1 Review)
3.1
eduardoherreral4

Greetings! I can help you with your project at a high level! I have lots of experience in SQL server, T-SQL. My skills and experience is a great match for your needs. Looking forward to hearing from you. Best regards More

$120 USD in 1 day
(4 Reviews)
2.8
CourserXu

Hello there, I have read your requirements and am confident I am the right candidate for the job. Among other things, I have over ten years of experience in Oracle PLSQL, PostgreSQL, MySQL, MS SQL, and others. Furtherm More

$20 USD in 7 days
(1 Review)
0.0
madhavparashar

Hi, I am having good experience in Microsoft SQL server. Please go though my profile and if you think that I am the best fit for your requirements, then please let me know. Thanks Madhav

$25 USD in 15 days
(0 Reviews)
0.0