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

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" <sergea@nospam.ehmail.com> wrote in message
news:mNnoe.21852$Or5.1207629@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
>
>

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]

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.

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--" <jcelko212@earthlink.net> wrote in message
news:1117930866.608318.77020@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.

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" <sergea@nospam.ehmail.com> wrote in message
news:N5qoe.21923$Or5.1243216@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]
>
>
>

serge (sergea@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, esquel@sommarskog.se

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

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

serge (sergea@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, esquel@sommarskog.se

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

> 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