|
|
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.
Navigation:
[Reply to this message]
|