|
|
Posted by David Portas on 09/08/07 09:43
"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns99A56D13F4F0DYazorman@127.0.0.1...
> 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
Thanks Erland. My mistake.
--
David Portas
Navigation:
[Reply to this message]
|