UPDATE/INSERT to make One-to-Many table become One-to-One

[serge]

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

[John Bell]

Hi

You can rename your table tblproducts using sp_rename then use something
like:

INSERT INTO [dbo].[tblProducts] ( [UniqueID], [Name], TagId )
select (SELECT COUNT(*)
from [dbo].[oldtblProducts] Q
JOIN [dbo].tblProductDetails E ON E.[ProductID] = Q.[UniqueID]
WHERE D.[ProductID] > E.[ProductID]
OR ( D.[ProductID] = E.[ProductID] AND D.Name > E.Name ) ) + 1 AS UniqueID,
D.Name,P.TagNo
from [dbo].[oldtblProducts] P
JOIN [dbo].tblProductDetails D ON D.[ProductID] = P.[UniqueID]

John

"serge" <se****@nospam.ehmail.com> wrote in message
news:mN*********************@wagner.videotron.net. ..

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

[serge]

Hi John,

Thanks for the code.

I ran your code and even though the code looks very nice (a single Insert
statement), it doesn't work in my case. If you compare my tblProductsResult
and the result generated by your code, the UniqueIDs are values that I can't
modify for the already existing records in tblProducts because they are
referenced
in other tables. My tblProducts UniqueID 4 is "JKL", after running your code
the
UniqueID 4 is "GHI2" which would cause problems for me.

I also completely forgot to mention a bigger problem in my original post.
I would also need to update the ProductID values in tblProductDetails for
all
the records that are being created in tblProducts. So everytime a new record
is created in tblProducts I will need to get the new Identity value of
tblProducts
and update the ProductID in tblProductDetails.

I also just noticed the code I had originally posted doesn't have the
product
'MNO' for my sample example.

Here's the whole new code again if anyone is interested to help me out.
I thought there could be a way to do this using a few update and insert
statements.
I suspect now that maybe it is possible but much harder to write than to
write
some type of a looping through the records one by one cursor and do
UPDATE or INSERT statements one record at a time.

Here's the current code I have for the sample records:

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)
INSERT INTO tblProducts VALUES (5, 'MNO', 99)

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, 6, 'GHIa')
INSERT INTO tblProductComponentsResult VALUES (12, 7, 'GHIa')
INSERT INTO tblProductComponentsResult VALUES (13, 8, 'JKLa')
INSERT INTO tblProductComponentsResult VALUES (14, 8, 'JKLb')
INSERT INTO tblProductComponentsResult VALUES (15, 9, 'JKLa')
INSERT INTO tblProductComponentsResult VALUES (16, 9, 'JKLb')
INSERT INTO tblProductComponentsResult VALUES (17, 10, 'JKLa')
INSERT INTO tblProductComponentsResult VALUES (18, 10, '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, 'MNO', 99)
INSERT INTO tblProductsResult VALUES (6, 'GHI2', 77)
INSERT INTO tblProductsResult VALUES (7, 'GHI3', 77)
INSERT INTO tblProductsResult VALUES (8, 'JKL2', 88)
INSERT INTO tblProductsResult VALUES (9, 'JKL3', 88)
INSERT INTO tblProductsResult VALUES (10, 'JKL4', 88)
CREATE TABLE [dbo].[tblProductDetailsResult] (
[UniqueID] [int] NOT NULL PRIMARY KEY ,
[Name] [varchar] (80) NULL,
[ProductID] int
) ON [PRIMARY]
GO

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

You can rename your table tblproducts using sp_rename then use something
like:

INSERT INTO [dbo].[tblProducts] ( [UniqueID], [Name], TagId )
select (SELECT COUNT(*)
from [dbo].[oldtblProducts] Q
JOIN [dbo].tblProductDetails E ON E.[ProductID] = Q.[UniqueID]
WHERE D.[ProductID] > E.[ProductID]
OR ( D.[ProductID] = E.[ProductID] AND D.Name > E.Name ) ) + 1 AS UniqueID, D.Name,P.TagNo
from [dbo].[oldtblProducts] P
JOIN [dbo].tblProductDetails D ON D.[ProductID] = P.[UniqueID]

[--CELKO--]

You have NULL-able columns for everything, no DRI to enforce the
1-to-many relationship you say is there and no relational keys
(IDENTITY is an exposed physical locator and cannot be a key by
definition). Why do keep saying "records", when a table has rows,
which are completely different things?

The goal is to update the name of the Product by giving it the name of the first matching Name from ProductDetails <<

How do you define this matching? Since tables have no ordering what
does "first" mean? MIN()? That coudl be done with a UPDATE if you had
real keys.

Doesn't your industry have a standard part number system? Doesn't your
company use it or have one of their own? The classic schema design
for this problem usually looks more like this:

CREATE TABLE Products
(product_id INTEGER NOT NULL PRIMARY KEY, -- industry std?
product_name VARCHAR (80) NOT NULL, -- really that long?
tag_nbr INTEGER NOT NULL); -- no constraints?

CREATE TABLE ProductDetails
(product_id INTEGER NOT NULL
REFERENCES Products (product_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
detail_name VARCHAR(80) NOT NULL,
PRIMARY KEY (product_id, detail_name));

CREATE TABLE ProductComponents
(product_id INTEGER NOT NULL
REFERENCES Products (product_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
component_name VARCHAR(80) NOT NULL,
PRIMARY KEY (product_id, component_name));

Begin by loading Products and get rid of the IDENTITY column, of
course. Then load the other tables that reference it.

[serge]

I am currently working on upgrading a production database
from an older version to a new version. I inherited the processes
of how the upgrade is done currently. In this case this part a manual
"record cleanup" is the process and I need to fix the records one by
one so I wanted to find a code-based upgrade method instead.

The tables in this case are sample tables i created to try to match
the real tables I am dealing with. I can't make any changes to any of the
database structure. I only need to "clean" it up and prepare for the upgrade
scripts that are already written previously.

So the logical questions of why the tables are like this is not something
I can do to change/update or correct.

You gave me an idea that i didn't think of. Maybe I can use
UPDATE CASCADE on the tables to do some of the update work.

Thanks
"--CELKO--" <jc*******@earthlink.net> wrote in message
news:11*********************@o13g2000cwo.googlegro ups.com...

You have NULL-able columns for everything, no DRI to enforce the
1-to-many relationship you say is there and no relational keys
(IDENTITY is an exposed physical locator and cannot be a key by
definition). Why do keep saying "records", when a table has rows,
which are completely different things?

The goal is to update the name of the Product by giving it the name of

the first matching Name from ProductDetails <<
How do you define this matching? Since tables have no ordering what
does "first" mean? MIN()? That coudl be done with a UPDATE if you had
real keys.

Doesn't your industry have a standard part number system? Doesn't your
company use it or have one of their own? The classic schema design
for this problem usually looks more like this:

CREATE TABLE Products
(product_id INTEGER NOT NULL PRIMARY KEY, -- industry std?
product_name VARCHAR (80) NOT NULL, -- really that long?
tag_nbr INTEGER NOT NULL); -- no constraints?

CREATE TABLE ProductDetails
(product_id INTEGER NOT NULL
REFERENCES Products (product_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
detail_name VARCHAR(80) NOT NULL,
PRIMARY KEY (product_id, detail_name));

CREATE TABLE ProductComponents
(product_id INTEGER NOT NULL
REFERENCES Products (product_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
component_name VARCHAR(80) NOT NULL,
PRIMARY KEY (product_id, component_name));

Begin by loading Products and get rid of the IDENTITY column, of
course. Then load the other tables that reference it.

[John Bell]

Hi

It sounds like the easiest way to do this is to drop the FKs (ALTER TABLE
statement) or make them CASCADING. Then have a table of old keys and new
keys and issue an update statement to change the values. You can then insert
the new values that do not already exist.

John
"serge" <se****@nospam.ehmail.com> wrote in message
news:N5*********************@wagner.videotron.net. ..

Hi John,

Thanks for the code.

I ran your code and even though the code looks very nice (a single Insert
statement), it doesn't work in my case. If you compare my
tblProductsResult
and the result generated by your code, the UniqueIDs are values that I
can't
modify for the already existing records in tblProducts because they are
referenced
in other tables. My tblProducts UniqueID 4 is "JKL", after running your
code
the
UniqueID 4 is "GHI2" which would cause problems for me.

I also completely forgot to mention a bigger problem in my original post.
I would also need to update the ProductID values in tblProductDetails for
all
the records that are being created in tblProducts. So everytime a new
record
is created in tblProducts I will need to get the new Identity value of
tblProducts
and update the ProductID in tblProductDetails.

I also just noticed the code I had originally posted doesn't have the
product
'MNO' for my sample example.

Here's the whole new code again if anyone is interested to help me out.
I thought there could be a way to do this using a few update and insert
statements.
I suspect now that maybe it is possible but much harder to write than to
write
some type of a looping through the records one by one cursor and do
UPDATE or INSERT statements one record at a time.

Here's the current code I have for the sample records:

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)
INSERT INTO tblProducts VALUES (5, 'MNO', 99)

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, 6, 'GHIa')
INSERT INTO tblProductComponentsResult VALUES (12, 7, 'GHIa')
INSERT INTO tblProductComponentsResult VALUES (13, 8, 'JKLa')
INSERT INTO tblProductComponentsResult VALUES (14, 8, 'JKLb')
INSERT INTO tblProductComponentsResult VALUES (15, 9, 'JKLa')
INSERT INTO tblProductComponentsResult VALUES (16, 9, 'JKLb')
INSERT INTO tblProductComponentsResult VALUES (17, 10, 'JKLa')
INSERT INTO tblProductComponentsResult VALUES (18, 10, '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, 'MNO', 99)
INSERT INTO tblProductsResult VALUES (6, 'GHI2', 77)
INSERT INTO tblProductsResult VALUES (7, 'GHI3', 77)
INSERT INTO tblProductsResult VALUES (8, 'JKL2', 88)
INSERT INTO tblProductsResult VALUES (9, 'JKL3', 88)
INSERT INTO tblProductsResult VALUES (10, 'JKL4', 88)
CREATE TABLE [dbo].[tblProductDetailsResult] (
[UniqueID] [int] NOT NULL PRIMARY KEY ,
[Name] [varchar] (80) NULL,
[ProductID] int
) ON [PRIMARY]
GO

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

You can rename your table tblproducts using sp_rename then use something
like:

INSERT INTO [dbo].[tblProducts] ( [UniqueID], [Name], TagId )
select (SELECT COUNT(*)
from [dbo].[oldtblProducts] Q
JOIN [dbo].tblProductDetails E ON E.[ProductID] = Q.[UniqueID]
WHERE D.[ProductID] > E.[ProductID]
OR ( D.[ProductID] = E.[ProductID] AND D.Name > E.Name ) ) + 1 AS

UniqueID,

D.Name,P.TagNo
from [dbo].[oldtblProducts] P
JOIN [dbo].tblProductDetails D ON D.[ProductID] = P.[UniqueID]

[Erland Sommarskog]

serge (se****@nospam.ehmail.com) writes:

I ran your code and even though the code looks very nice (a single
Insert statement), it doesn't work in my case. If you compare my
tblProductsResult and the result generated by your code, the UniqueIDs
are values that I can't modify for the already existing records in
tblProducts because they are referenced in other tables. My tblProducts
UniqueID 4 is "JKL", after running your code the UniqueID 4 is "GHI2"
which would cause problems for me.

I also completely forgot to mention a bigger problem in my original
post. I would also need to update the ProductID values in
tblProductDetails for all the records that are being created in
tblProducts. So everytime a new record is created in tblProducts I will
need to get the new Identity value of tblProducts and update the
ProductID in tblProductDetails.

I also just noticed the code I had originally posted doesn't have the
product 'MNO' for my sample example.

Here's the whole new code again if anyone is interested to help me out.
I thought there could be a way to do this using a few update and insert
statements. I suspect now that maybe it is possible but much harder to
write than to write some type of a looping through the records one by
one cursor and do UPDATE or INSERT statements one record at a time.

Below is a script that almost produces the result you are asking for.
The difference from your reuslts table is that Product 4 now gets the
name JKL2, and JKL is at Product 8. But since 4 and 8 are clones of
each other, my guess is that it doesn't matter. If it does, it can
probably be handled, but I didn't want to spend time to find how it
it's not needed.

-- A mapping table from old product id to new product id, as well as from
-- detail id to new product id. The ident colunm is there to help us with
-- the new ids.
CREATE TABLE #newprod(ident int IDENTITY,
oldprodid int NOT NULL,
detailid int NOT NULL,
name varchar(80) NOT NULL,
newprodid int NULL)

-- Insert all details where there detail id is bigger than the min
-- detail-id for for a product.
INSERT #newprod (oldprodid, detailid, name)
SELECT a.ProductID, a.UniqueID, a.Name
FROM tblProductDetails a
WHERE NOT EXISTS (SELECT *
FROM (SELECT id = MIN(UniqueID)
FROM tblProductDetails
GROUP BY ProductID) AS b
WHERE a.UniqueID = b.id)
ORDER BY a.UniqueID

-- Set new the productid.
UPDATE #newprod
SET newprodid = n.ident + (SELECT MAX(UniqueID) FROM tblProducts)
FROM #newprod n

-- Create the clones. (If the real-world table has IDENTITY, you need
-- SET IDENTITY_INSERT ON here.
INSERT tblProducts (UniqueID, Name, TagNo)
SELECT n.newprodid, p.Name, p.TagNo
FROM #newprod n
JOIN tblProducts p ON n.oldprodid = p.UniqueID

-- Update details table with the new Product ids.
UPDATE tblProductDetails
SET ProductID = n.newprodid
FROM tblProductDetails d
JOIN #newprod n ON d.UniqueID = n.detailid

-- Set product name to match details.
UPDATE tblProducts
SET Name = d.Name
FROM tblProducts p
JOIN tblProductDetails d ON p.UniqueID = d.ProductID

-- And go on to clone components. Again there is an intermediate table
-- to get the new identity values. Had the Component tables had an
-- IDENTITY column, we would not need this table.
CREATE TABLE #newcomp(ident int IDENTITY,
newprodid int NOT NULL,
component varchar(80) NOT NULL)

INSERT #newcomp(newprodid, component)
SELECT n.newprodid, c.Component
FROM tblProductComponents c
JOIN #newprod n ON c.ProductID = n.oldprodid
ORDER BY n.newprodid, c.Component

INSERT tblProductComponents (UniqueID, ProductID, Component)
SELECT n.ident + m.maxid, n.newprodid, n.component
FROM #newcomp n
CROSS JOIN (SELECT maxid = MAX(UniqueID)
FROM tblProductComponents) AS m

-- Look at the result
SELECT * FROM tblProducts ORDER BY UniqueID
SELECT * FROM tblProductDetails ORDER BY UniqueID
SELECT * FROM tblProductComponents ORDER BY UniqueID


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[serge]

> The difference from your reuslts table is that Product 4 now gets the

name JKL2, and JKL is at Product 8. But since 4 and 8 are clones of
each other, my guess is that it doesn't matter.

I am 99% sure it does not matter. I'll find out soon if it matters but I
doubt it.

I tried your code and it works great. I analyzed it for the last hour to
understand what you were doing. Now I understand and I didn't consider
before using a temporary table like you are using. I was planning on
using a cursor and loop through the records one by one which would have
been much slower than your approach. Not to mention it would have been
complicated also.

Next time I'll think about using temporary tables as I realize now how
much flexibility it gave you to do the gradual data manipulations.
Thanks a lot Erland, I appreciate your help.

[Erland Sommarskog]

serge (se****@nospam.ehmail.com) writes:

I tried your code and it works great. I analyzed it for the last hour to
understand what you were doing. Now I understand and I didn't consider
before using a temporary table like you are using. I was planning on
using a cursor and loop through the records one by one which would have
been much slower than your approach. Not to mention it would have been
complicated also.

Next time I'll think about using temporary tables as I realize now how
much flexibility it gave you to do the gradual data manipulations.

The true die-hards does of course try to all in one query, and stay
away from temp tables. I am more pragmatic, and interested in getting
the job done as effectively as possible.

The problem could probably be solved with out temp tables, if you don't
care about the new ids to be consecutive. The new product ids could be
formed by adding MAX(ProductID) to the detail id. But since you real
tables have IDENTITY, I figured that you could get into some problems
with that.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[serge]

> Below is a script that almost produces the result you are asking for.

The difference from your reuslts table is that Product 4 now gets the
name JKL2, and JKL is at Product 8. But since 4 and 8 are clones of
each other, my guess is that it doesn't matter. If it does, it can
probably be handled, but I didn't want to spend time to find how it
it's not needed.
Actually it seems that the name does matter. There is a unique index on
the NAME column on tblProducts. You make your code look easy and
simple to write. I tried to modify this part of the code below to exclude
matching names from the selection list by adding a JOIN to tblProducts.
I was unsuccessful after 3 hours of trying. I have people pressing me to
give up writing code and upgrade the data manually one record at a time.
What a nightmare!

I then thought about deleting temporarily the Unique Index and running the
code which will create duplicate records without any error. Then the UPDATE
Names code I believe is fixing/patching things up. However i am at this
point
unsure if i will have to face a new problem when it comes to the other CHILD
table references that I have to create clone records.
SELECT a.ProductID, a.UniqueID, a.Name
FROM tblProductDetails a
WHERE NOT EXISTS (SELECT *
FROM (SELECT id = MIN(UniqueID)
FROM tblProductDetails
GROUP BY ProductID) AS b
WHERE a.UniqueID = b.id)
ORDER BY a.UniqueID

If you believe you can update the code very easily to handle the Name
matter,
would it be possible to do it please?

Thank you

[Erland Sommarskog]

serge (se****@nospam.ehmail.com) writes:

Actually it seems that the name does matter. There is a unique index on
the NAME column on tblProducts. You make your code look easy and
simple to write. I tried to modify this part of the code below to exclude
matching names from the selection list by adding a JOIN to tblProducts.
I was unsuccessful after 3 hours of trying. I have people pressing me to
give up writing code and upgrade the data manually one record at a time.
What a nightmare!

I then thought about deleting temporarily the Unique Index and running
the code which will create duplicate records without any error. Then the
UPDATE Names code I believe is fixing/patching things up. However i am
at this point unsure if i will have to face a new problem when it comes
to the other CHILD table references that I have to create clone
records.

So here is a quick fix, that evades the problem with the constraint,
but still moves the id:

-- Create the clones. (If the real-world table has IDENTITY, you need
-- SET IDENTITY_INSERT ON here.
INSERT tblProducts (UniqueID, Name, TagNo)
SELECT n.newprodid, newid(), p.TagNo
FROM #newprod n
JOIN tblProducts p ON n.oldprodid = p.UniqueID

That is, I'm creating the clones with dummy names, to fix those
later.

I looked into preserving the name entire, but that was a little bit
tricky at this time of night.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[serge]

> So here is a quick fix, that evades the problem with the constraint,

but still moves the id:

-- Create the clones. (If the real-world table has IDENTITY, you need
-- SET IDENTITY_INSERT ON here.
INSERT tblProducts (UniqueID, Name, TagNo)
SELECT n.newprodid, newid(), p.TagNo
FROM #newprod n
JOIN tblProducts p ON n.oldprodid = p.UniqueID

That is, I'm creating the clones with dummy names, to fix those
later.
Thanks again for helping me.

What you are doing is nice. Something i didn't think of. Instead of
temporarily deleting the Index I could use your method.

However this still doesn't solve the problem with the name.

I looked into preserving the name entire, but that was a little bit
tricky at this time of night.

I continued this from home and it is much easier working at home than
at work. I can think without any disruption.

I believe I have the code that returns all the records that I will
need to ADD as new records and not worry of the name issue.

I am replacing this block of code:

-- Insert all details where there detail id is bigger than the min
-- detail-id for for a product.
INSERT #newprod (oldprodid, detailid, name)
SELECT a.ProductID, a.UniqueID, a.Name
FROM tblProductDetails a
WHERE NOT EXISTS (SELECT *
FROM (SELECT id = MIN(UniqueID)
FROM tblProductDetails
GROUP BY ProductID) AS b
WHERE a.UniqueID = b.id)
ORDER BY a.UniqueID
with this new one:

-- Insert all details where there detail id is bigger than the min
-- detail-id for for a product.
INSERT #newprod (oldprodid, detailid, name)
SELECT a.ProductID, a.UniqueID, a.Name
FROM

(SELECT tblProductDetails.ProductId, tblProductDetails.UniqueId,
tblProductDetails.Name
FROM tblProductDetails INNER JOIN tblProducts
ON tblProductDetails.ProductId = tblProducts.UniqueId
WHERE tblProductDetails.Name <> tblProducts.Name) AS a

INNER JOIN

(SELECT ProductID
FROM tblProductDetails
GROUP BY ProductID
HAVING COUNT(*) > 1) AS b
ON a.ProductId = b.ProductId

ORDER BY a.UniqueID
Basically my first derived table "a" returns me all tblProductDetails where
the
Name is different than the Name in tblProducts.

Then I need to reduce the list of the derived table "a" to those records
that
exist more than 1 record (JOIN to derived table "b").
Anyhow I think the name problem is resolved. I'll continue working and I'll
see
how things go. I still have some other conditions that I'll have to decide
if I
can deal with code or I have to manually fix it.

Thanks Erland.

[--CELKO--]

Hope I helped a little bit.

The fifth labor of Hercules was to clean the stables of King Augeas in
a single day. The Augean stables held thousands of animals and were
over a mile long. This story has a happy ending for three reasons: (1)
Hercules solved the problem in a clever way (2) Hercules got one tenth
of the cattle for his work (3) At the end of the story of the Labors of
Hercules, he got to kill the bastard that gave him this job.

[Erland Sommarskog]

serge (se****@nospam.ehmail.com) writes:

with this new one:

-- Insert all details where there detail id is bigger than the min
-- detail-id for for a product.
INSERT #newprod (oldprodid, detailid, name)
SELECT a.ProductID, a.UniqueID, a.Name
FROM

(SELECT tblProductDetails.ProductId, tblProductDetails.UniqueId,
tblProductDetails.Name
FROM tblProductDetails INNER JOIN tblProducts
ON tblProductDetails.ProductId = tblProducts.UniqueId
WHERE tblProductDetails.Name <> tblProducts.Name) AS a

INNER JOIN

(SELECT ProductID
FROM tblProductDetails
GROUP BY ProductID
HAVING COUNT(*) > 1) AS b
ON a.ProductId = b.ProductId

ORDER BY a.UniqueID
Basically my first derived table "a" returns me all tblProductDetails
where the Name is different than the Name in tblProducts.

Then I need to reduce the list of the derived table "a" to those records
that exist more than 1 record (JOIN to derived table "b").

Excellent! Don't know if I would have been able to come with that
one by myself. (Well, maybe I would, but I was not thinking those
directions.)

Here is a rewrite of the query, more for clarity to tell exactly
what is going on: Save all product details where the name differs
from the product, for which there is more than one row for the
product:

INSERT #newprod (oldprodid, detailid, name)
SELECT a.ProductID, a.UniqueID, a.Name
FROM (SELECT d.ProductID, d.UniqueID, d.Name
FROM tblProductDetails d
JOIN tblProducts p ON d.ProductID = p.UniqueID
WHERE d.Name <> p.Name) AS a
WHERE EXISTS (SELECT *
FROM (SELECT ProductID
FROM tblProductDetails
GROUP BY ProductID
HAVING COUNT(*) > 1) AS b
WHERE a.ProductID = b.ProductID)
ORDER BY a.UniqueID

Whether one likes alias or is partly a matter of taste, but I think
the code is more consice and easier to read this way.

Thanks for posting back the final result!

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[serge]

> Here is a rewrite of the query, more for clarity to tell exactly

what is going on: Save all product details where the name differs
from the product, for which there is more than one row for the
product:

Thanks Erland, I will look at this new code.