UPDATE/INSERT To Make One-to-Many Table Become One-to-One

I have a scenario where two tables are in a One-to-Many relationship
and I need to move the data from the Many table to the One table so
that it becomes a One-to-One relationship.

I need to salvage the records from the many table and without going
into detail, one of the reasons I can't do the opposite as
there are records in the ONE table that I need to keep even if they
don't have any child records in the MANY table.

Below I created the code to create the sample tables:

1- tblProducts is the ONE side table
2- tblProductDetails is the MANY side table
3- tblProductsResult is the RESULT I expect to get after running
some T-SQL code
4- tblProductComponents is another MANY side table to tblProducts
5- tblProductComponentsResult is the RESULT I expect to get...

Some of the points to consider:
6- Normally all UniqueID columns are to be IDENTITY. For
this sample i am entering the UniqueID values myself.
7- I don't want to create new tables like tblProductsResult
and tblProductComponentsResult. I want to update the real tables.
I have created the tblxxxResult tables only for this post.
8- The goal is to update the name of the Product by giving it the
name of the first matching Name from tblProductDetails.
9- If there are more than one entry in tblProductDetails for each
Product, then I need to create new Products inheriting the original
Product's information including its child records from tblProductComponents.

If you run the code and open the tables it will be much clearer
to visually see what I want to achieve.

CREATE DATABASE MyTestDB
GO
USE MyTestDB
GO

CREATE TABLE [dbo].[tblProducts] (
[UniqueID] [int] NOT NULL PRIMARY KEY ,
[Name] [varchar] (80) NULL,
[TagNo] [int] NULL
) ON [PRIMARY]
GO

INSERT INTO tblProducts VALUES (1, 'ABC', 55)
INSERT INTO tblProducts VALUES (2, 'DEF', 66)
INSERT INTO tblProducts VALUES (3, 'GHI', 77)
INSERT INTO tblProducts VALUES (4, 'JKL', 88)

CREATE TABLE [dbo].[tblProductDetails] (
[UniqueID] [int] NOT NULL PRIMARY KEY ,
[Name] [varchar] (80) NULL,
[ProductID] int
) ON [PRIMARY]
GO

INSERT INTO tblProductDetails VALUES (1, 'ABC1', 1)
INSERT INTO tblProductDetails VALUES (2, 'DEF', 2)
INSERT INTO tblProductDetails VALUES (3, 'GHI', 3)
INSERT INTO tblProductDetails VALUES (4, 'GHI2', 3)
INSERT INTO tblProductDetails VALUES (5, 'GHI3', 3)
INSERT INTO tblProductDetails VALUES (6, 'JKL2', 4)
INSERT INTO tblProductDetails VALUES (7, 'JKL', 4)
INSERT INTO tblProductDetails VALUES (8, 'JKL3', 4)
INSERT INTO tblProductDetails VALUES (9, 'JKL4', 4)

CREATE TABLE [dbo].[tblProductComponents] (
[UniqueID] [int] NOT NULL PRIMARY KEY ,
[ProductID] int,
[Component] [varchar] (80) NULL
) ON [PRIMARY]
GO

INSERT INTO tblProductComponents VALUES (1, 1, 'ABCa')
INSERT INTO tblProductComponents VALUES (2, 1, 'ABCb')
INSERT INTO tblProductComponents VALUES (3, 1, 'ABCc')
INSERT INTO tblProductComponents VALUES (4, 2, 'DEFa')
INSERT INTO tblProductComponents VALUES (5, 2, 'DEFb')
INSERT INTO tblProductComponents VALUES (6, 2, 'DEFc')
INSERT INTO tblProductComponents VALUES (7, 2, 'DEFd')
INSERT INTO tblProductComponents VALUES (8, 3, 'GHIa')
INSERT INTO tblProductComponents VALUES (9, 4, 'JKLa')
INSERT INTO tblProductComponents VALUES (10, 4, 'JKLb')

CREATE TABLE [dbo].[tblProductComponentsResult] (
[UniqueID] [int] NOT NULL PRIMARY KEY ,
[ProductID] int,
[Component] [varchar] (80) NULL
) ON [PRIMARY]
GO

INSERT INTO tblProductComponentsResult VALUES (1, 1, 'ABCa')
INSERT INTO tblProductComponentsResult VALUES (2, 1, 'ABCb')
INSERT INTO tblProductComponentsResult VALUES (3, 1, 'ABCc')
INSERT INTO tblProductComponentsResult VALUES (4, 2, 'DEFa')
INSERT INTO tblProductComponentsResult VALUES (5, 2, 'DEFb')
INSERT INTO tblProductComponentsResult VALUES (6, 2, 'DEFc')
INSERT INTO tblProductComponentsResult VALUES (7, 2, 'DEFd')
INSERT INTO tblProductComponentsResult VALUES (8, 3, 'GHIa')
INSERT INTO tblProductComponentsResult VALUES (9, 4, 'JKLa')
INSERT INTO tblProductComponentsResult VALUES (10, 4, 'JKLb')
INSERT INTO tblProductComponentsResult VALUES (11, 5, 'GHIa')
INSERT INTO tblProductComponentsResult VALUES (12, 6, 'GHIa')
INSERT INTO tblProductComponentsResult VALUES (13, 7, 'JKLa')
INSERT INTO tblProductComponentsResult VALUES (14, 7, 'JKLb')
INSERT INTO tblProductComponentsResult VALUES (15, 8, 'JKLa')
INSERT INTO tblProductComponentsResult VALUES (16, 8, 'JKLb')
INSERT INTO tblProductComponentsResult VALUES (17, 9, 'JKLa')
INSERT INTO tblProductComponentsResult VALUES (18, 9, 'JKLb')


CREATE TABLE [dbo].[tblProductsResult] (
[UniqueID] [int] NOT NULL PRIMARY KEY ,
[Name] [varchar] (80) NULL,
[TagNo] [int] NULL
) ON [PRIMARY]
GO

INSERT INTO tblProductsResult VALUES (1, 'ABC1', 55)
INSERT INTO tblProductsResult VALUES (2, 'DEF', 66)
INSERT INTO tblProductsResult VALUES (3, 'GHI', 77)
INSERT INTO tblProductsResult VALUES (4, 'JKL', 88)
INSERT INTO tblProductsResult VALUES (5, 'GHI2', 77)
INSERT INTO tblProductsResult VALUES (6, 'GHI3', 77)
INSERT INTO tblProductsResult VALUES (7, 'JKL2', 88)
INSERT INTO tblProductsResult VALUES (8, 'JKL3', 88)
INSERT INTO tblProductsResult VALUES (9, 'JKL4', 88)


I appreciate your assistance on this.


Thank you very much