Posted by serge on 06/07/05 04:24

> So here is a quick fix, that evades the problem with the constraint,
> but still moves the id:
>
> -- 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, newid(), p.TagNo
> FROM #newprod n
> JOIN tblProducts p ON n.oldprodid = p.UniqueID
>
> That is, I'm creating the clones with dummy names, to fix those
> later.

Thanks again for helping me.

What you are doing is nice. Something i didn't think of. Instead of
temporarily deleting the Index I could use your method.

However this still doesn't solve the problem with the name.


> I looked into preserving the name entire, but that was a little bit
> tricky at this time of night.

I continued this from home and it is much easier working at home than
at work. I can think without any disruption.

I believe I have the code that returns all the records that I will
need to ADD as new records and not worry of the name issue.

I am replacing this block of code:

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


with this new one:

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

(SELECT tblProductDetails.ProductId, tblProductDetails.UniqueId,
tblProductDetails.Name
FROM tblProductDetails INNER JOIN tblProducts
ON tblProductDetails.ProductId = tblProducts.UniqueId
WHERE tblProductDetails.Name <> tblProducts.Name) AS a

INNER JOIN

(SELECT ProductID
FROM tblProductDetails
GROUP BY ProductID
HAVING COUNT(*) > 1) AS b
ON a.ProductId = b.ProductId

ORDER BY a.UniqueID


Basically my first derived table "a" returns me all tblProductDetails where
the
Name is different than the Name in tblProducts.

Then I need to reduce the list of the derived table "a" to those records
that
exist more than 1 record (JOIN to derived table "b").


Anyhow I think the name problem is resolved. I'll continue working and I'll
see
how things go. I still have some other conditions that I'll have to decide
if I
can deal with code or I have to manually fix it.

Thanks Erland.

[Back to original message]