Posted by Erland Sommarskog on 06/05/05 18:40

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/techinfo/productdoc/2000/books.asp

[Back to original message]