Hi:
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.

"Hamilton sucks" <caof (AT) mcmaster (DOT) ca> wrote

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?

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 ;

--
David Portas

David Portas (REMOVE_BEFORE_REPLYING_dportas (AT) acm (DOT) org) writes:
Maybe he is generating test data?

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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

"Erland Sommarskog" <esquel (AT) sommarskog (DOT) se> wrote


Thanks Erland. My mistake.

--
David Portas

On Sep 7, 6:08 pm, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor... (AT) acm (DOT) org> wrote:
Thanks, david for your help. The reason for copying records is that I
need to change them to new records, which are exactly the same as the
old records except one column value. The loc_id is the primary key but
it's not really used for searching information. So basically I don't
care about the loc_id as long as they are unique, that 's why i want
it to be random.

On Sep 8, 4:38 am, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:
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.

"Hamilton sucks" <caof (AT) mcmaster (DOT) ca> wrote

Then they are not exactly the same. Insert the new rows together including
the NEW column value(s). That way you can add any relevent candidate key
constraints to your table - something that wouldn't be possible if the table
had to support transitional "copies" of the old data. Example:

INSERT INTO tbl (col1, col2, col3)
SELECT col1, @new_col2, col3
FROM tbl
WHERE ... ? ;

This sounds very like a multi-valued or join-dependency situation. Are you
familiar with the Fourth and Fifth Normal Forms? If not then look up some
examples. You should satisfy yourself about the design based on your own
understanding of the business rules. It's notoriously difficult to give
detailed design advice in an online discussion. (Easy to spot potential
problems but hard to suggest the right solutions).

--
David Portas