Posted by John Bell on 06/05/05 12:42

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

[Back to original message]