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- tblProductDetai ls is the MANY side table
3- tblProductsResu lt is the RESULT I expect to get after running
some T-SQL code
4- tblProductCompo nents is another MANY side table to tblProducts
5- tblProductCompo nentsResult 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 tblProductsResu lt
and tblProductCompo nentsResult. 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 tblProductDetai ls.
9- If there are more than one entry in tblProductDetai ls for each
Product, then I need to create new Products inheriting the original
Product's information including its child records from tblProductCompo nents.
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].[tblProductDetai ls] (
[UniqueID] [int] NOT NULL PRIMARY KEY ,
[Name] [varchar] (80) NULL,
[ProductID] int
) ON [PRIMARY]
GO
INSERT INTO tblProductDetai ls VALUES (1, 'ABC1', 1)
INSERT INTO tblProductDetai ls VALUES (2, 'DEF', 2)
INSERT INTO tblProductDetai ls VALUES (3, 'GHI', 3)
INSERT INTO tblProductDetai ls VALUES (4, 'GHI2', 3)
INSERT INTO tblProductDetai ls VALUES (5, 'GHI3', 3)
INSERT INTO tblProductDetai ls VALUES (6, 'JKL2', 4)
INSERT INTO tblProductDetai ls VALUES (7, 'JKL', 4)
INSERT INTO tblProductDetai ls VALUES (8, 'JKL3', 4)
INSERT INTO tblProductDetai ls VALUES (9, 'JKL4', 4)
CREATE TABLE [dbo].[tblProductCompo nents] (
[UniqueID] [int] NOT NULL PRIMARY KEY ,
[ProductID] int,
[Component] [varchar] (80) NULL
) ON [PRIMARY]
GO
INSERT INTO tblProductCompo nents VALUES (1, 1, 'ABCa')
INSERT INTO tblProductCompo nents VALUES (2, 1, 'ABCb')
INSERT INTO tblProductCompo nents VALUES (3, 1, 'ABCc')
INSERT INTO tblProductCompo nents VALUES (4, 2, 'DEFa')
INSERT INTO tblProductCompo nents VALUES (5, 2, 'DEFb')
INSERT INTO tblProductCompo nents VALUES (6, 2, 'DEFc')
INSERT INTO tblProductCompo nents VALUES (7, 2, 'DEFd')
INSERT INTO tblProductCompo nents VALUES (8, 3, 'GHIa')
INSERT INTO tblProductCompo nents VALUES (9, 4, 'JKLa')
INSERT INTO tblProductCompo nents VALUES (10, 4, 'JKLb')
CREATE TABLE [dbo].[tblProductCompo nentsResult] (
[UniqueID] [int] NOT NULL PRIMARY KEY ,
[ProductID] int,
[Component] [varchar] (80) NULL
) ON [PRIMARY]
GO
INSERT INTO tblProductCompo nentsResult VALUES (1, 1, 'ABCa')
INSERT INTO tblProductCompo nentsResult VALUES (2, 1, 'ABCb')
INSERT INTO tblProductCompo nentsResult VALUES (3, 1, 'ABCc')
INSERT INTO tblProductCompo nentsResult VALUES (4, 2, 'DEFa')
INSERT INTO tblProductCompo nentsResult VALUES (5, 2, 'DEFb')
INSERT INTO tblProductCompo nentsResult VALUES (6, 2, 'DEFc')
INSERT INTO tblProductCompo nentsResult VALUES (7, 2, 'DEFd')
INSERT INTO tblProductCompo nentsResult VALUES (8, 3, 'GHIa')
INSERT INTO tblProductCompo nentsResult VALUES (9, 4, 'JKLa')
INSERT INTO tblProductCompo nentsResult VALUES (10, 4, 'JKLb')
INSERT INTO tblProductCompo nentsResult VALUES (11, 5, 'GHIa')
INSERT INTO tblProductCompo nentsResult VALUES (12, 6, 'GHIa')
INSERT INTO tblProductCompo nentsResult VALUES (13, 7, 'JKLa')
INSERT INTO tblProductCompo nentsResult VALUES (14, 7, 'JKLb')
INSERT INTO tblProductCompo nentsResult VALUES (15, 8, 'JKLa')
INSERT INTO tblProductCompo nentsResult VALUES (16, 8, 'JKLb')
INSERT INTO tblProductCompo nentsResult VALUES (17, 9, 'JKLa')
INSERT INTO tblProductCompo nentsResult VALUES (18, 9, 'JKLb')
CREATE TABLE [dbo].[tblProductsResu lt] (
[UniqueID] [int] NOT NULL PRIMARY KEY ,
[Name] [varchar] (80) NULL,
[TagNo] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO tblProductsResu lt VALUES (1, 'ABC1', 55)
INSERT INTO tblProductsResu lt VALUES (2, 'DEF', 66)
INSERT INTO tblProductsResu lt VALUES (3, 'GHI', 77)
INSERT INTO tblProductsResu lt VALUES (4, 'JKL', 88)
INSERT INTO tblProductsResu lt VALUES (5, 'GHI2', 77)
INSERT INTO tblProductsResu lt VALUES (6, 'GHI3', 77)
INSERT INTO tblProductsResu lt VALUES (7, 'JKL2', 88)
INSERT INTO tblProductsResu lt VALUES (8, 'JKL3', 88)
INSERT INTO tblProductsResu lt VALUES (9, 'JKL4', 88)
I appreciate your assistance on this.
Thank you very much
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- tblProductDetai ls is the MANY side table
3- tblProductsResu lt is the RESULT I expect to get after running
some T-SQL code
4- tblProductCompo nents is another MANY side table to tblProducts
5- tblProductCompo nentsResult 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 tblProductsResu lt
and tblProductCompo nentsResult. 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 tblProductDetai ls.
9- If there are more than one entry in tblProductDetai ls for each
Product, then I need to create new Products inheriting the original
Product's information including its child records from tblProductCompo nents.
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].[tblProductDetai ls] (
[UniqueID] [int] NOT NULL PRIMARY KEY ,
[Name] [varchar] (80) NULL,
[ProductID] int
) ON [PRIMARY]
GO
INSERT INTO tblProductDetai ls VALUES (1, 'ABC1', 1)
INSERT INTO tblProductDetai ls VALUES (2, 'DEF', 2)
INSERT INTO tblProductDetai ls VALUES (3, 'GHI', 3)
INSERT INTO tblProductDetai ls VALUES (4, 'GHI2', 3)
INSERT INTO tblProductDetai ls VALUES (5, 'GHI3', 3)
INSERT INTO tblProductDetai ls VALUES (6, 'JKL2', 4)
INSERT INTO tblProductDetai ls VALUES (7, 'JKL', 4)
INSERT INTO tblProductDetai ls VALUES (8, 'JKL3', 4)
INSERT INTO tblProductDetai ls VALUES (9, 'JKL4', 4)
CREATE TABLE [dbo].[tblProductCompo nents] (
[UniqueID] [int] NOT NULL PRIMARY KEY ,
[ProductID] int,
[Component] [varchar] (80) NULL
) ON [PRIMARY]
GO
INSERT INTO tblProductCompo nents VALUES (1, 1, 'ABCa')
INSERT INTO tblProductCompo nents VALUES (2, 1, 'ABCb')
INSERT INTO tblProductCompo nents VALUES (3, 1, 'ABCc')
INSERT INTO tblProductCompo nents VALUES (4, 2, 'DEFa')
INSERT INTO tblProductCompo nents VALUES (5, 2, 'DEFb')
INSERT INTO tblProductCompo nents VALUES (6, 2, 'DEFc')
INSERT INTO tblProductCompo nents VALUES (7, 2, 'DEFd')
INSERT INTO tblProductCompo nents VALUES (8, 3, 'GHIa')
INSERT INTO tblProductCompo nents VALUES (9, 4, 'JKLa')
INSERT INTO tblProductCompo nents VALUES (10, 4, 'JKLb')
CREATE TABLE [dbo].[tblProductCompo nentsResult] (
[UniqueID] [int] NOT NULL PRIMARY KEY ,
[ProductID] int,
[Component] [varchar] (80) NULL
) ON [PRIMARY]
GO
INSERT INTO tblProductCompo nentsResult VALUES (1, 1, 'ABCa')
INSERT INTO tblProductCompo nentsResult VALUES (2, 1, 'ABCb')
INSERT INTO tblProductCompo nentsResult VALUES (3, 1, 'ABCc')
INSERT INTO tblProductCompo nentsResult VALUES (4, 2, 'DEFa')
INSERT INTO tblProductCompo nentsResult VALUES (5, 2, 'DEFb')
INSERT INTO tblProductCompo nentsResult VALUES (6, 2, 'DEFc')
INSERT INTO tblProductCompo nentsResult VALUES (7, 2, 'DEFd')
INSERT INTO tblProductCompo nentsResult VALUES (8, 3, 'GHIa')
INSERT INTO tblProductCompo nentsResult VALUES (9, 4, 'JKLa')
INSERT INTO tblProductCompo nentsResult VALUES (10, 4, 'JKLb')
INSERT INTO tblProductCompo nentsResult VALUES (11, 5, 'GHIa')
INSERT INTO tblProductCompo nentsResult VALUES (12, 6, 'GHIa')
INSERT INTO tblProductCompo nentsResult VALUES (13, 7, 'JKLa')
INSERT INTO tblProductCompo nentsResult VALUES (14, 7, 'JKLb')
INSERT INTO tblProductCompo nentsResult VALUES (15, 8, 'JKLa')
INSERT INTO tblProductCompo nentsResult VALUES (16, 8, 'JKLb')
INSERT INTO tblProductCompo nentsResult VALUES (17, 9, 'JKLa')
INSERT INTO tblProductCompo nentsResult VALUES (18, 9, 'JKLb')
CREATE TABLE [dbo].[tblProductsResu lt] (
[UniqueID] [int] NOT NULL PRIMARY KEY ,
[Name] [varchar] (80) NULL,
[TagNo] [int] NULL
) ON [PRIMARY]
GO
INSERT INTO tblProductsResu lt VALUES (1, 'ABC1', 55)
INSERT INTO tblProductsResu lt VALUES (2, 'DEF', 66)
INSERT INTO tblProductsResu lt VALUES (3, 'GHI', 77)
INSERT INTO tblProductsResu lt VALUES (4, 'JKL', 88)
INSERT INTO tblProductsResu lt VALUES (5, 'GHI2', 77)
INSERT INTO tblProductsResu lt VALUES (6, 'GHI3', 77)
INSERT INTO tblProductsResu lt VALUES (7, 'JKL2', 88)
INSERT INTO tblProductsResu lt VALUES (8, 'JKL3', 88)
INSERT INTO tblProductsResu lt VALUES (9, 'JKL4', 88)
I appreciate your assistance on this.
Thank you very much
Comment