|
|
Posted by Hamilton sucks on 09/08/07 17:52
On Sep 8, 4:38 am, Erland Sommarskog <esq...@sommarskog.se> wrote:
> David Portas (REMOVE_BEFORE_REPLYING_dpor...@acm.org) writes:
> > "Hamilton sucks" <c...@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, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Maybe I should consult about the design. The table location holds the
location info about the local schools, each of them is linked to a
test group and a set of students. If I want to add a new test group,
which contains the same set of locations except that they are linked
to the new group. Should I create a new table or add new records into
tbl_location? The same problem holds for tbl_students as well since in
the new group, all students' status must be reset.
Navigation:
[Reply to this message]
|