|
|
Posted by John Bell on 06/05/05 00:29
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
>
>
Navigation:
[Reply to this message]
|