Posted by serge on 06/05/05 01:40

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]

[Back to original message]