Posted by Erland Sommarskog on 09/08/07 08:38

David Portas (REMOVE_BEFORE_REPLYING_dportas@acm.org) writes:
> "Hamilton sucks" <caof@mcmaster.ca> wrote in message
> news:1189201676.191048.77380@22g2000hsm.googlegroups.com...
>> I need to add some records in a table called location(primary key:
>> loc_id). What I want to do is for each location in the table, I add
>> the same record but with a different loc_id, which can be a random
>> string. All the other column should contain the same value. Can anyone
>> give me a hint on how to do this in SQL server 2000 enterprise
>> manager?
>> thx.
>>
>
> A strange design. If the only key is random then how do you hope to
> retrieve the information? If the rest of the data is to be identical
> then why bother copying it?

Maybe he is generating test data?

> DECLARE @loc_id VARCHAR(36);
> SET @loc_id = CAST(NEWID() AS VARCHAR(36));
>
> INSERT INTO location (@loc_id, col1, col2, ...)
> SELECT col1, col2, ...
> FROM location ;

That does not look like it would work out. :-)

As I understand Hamilton, he wants each copied row to have each own
new id. Using newid() this would be:

INSERT location (loc_id, col1, col2, ...)
SELECT convert(char(36), newid()), col1, col2, ....
FROM location


Obviously, this will not work if loc_id is shorter than 36 characters.
Hamilton could use substring, but obviously the short loc_id is the
bigger the possibility for duplicates.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]