Posted by Erland Sommarskog on 06/07/05 01:56

serge (sergea@nospam.ehmail.com) writes:
> Actually it seems that the name does matter. There is a unique index on
> the NAME column on tblProducts. You make your code look easy and
> simple to write. I tried to modify this part of the code below to exclude
> matching names from the selection list by adding a JOIN to tblProducts.
> I was unsuccessful after 3 hours of trying. I have people pressing me to
> give up writing code and upgrade the data manually one record at a time.
> What a nightmare!
>
> I then thought about deleting temporarily the Unique Index and running
> the code which will create duplicate records without any error. Then the
> UPDATE Names code I believe is fixing/patching things up. However i am
> at this point unsure if i will have to face a new problem when it comes
> to the other CHILD table references that I have to create clone
> records.

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.

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



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