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@.mcmaster.cawrote in message
news:1189201676.191048.77380@.22g2000hsm.googlegrou ps.com...
Quote:
Originally Posted by
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.
>
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@.acm.org) writes:
Quote:
Originally Posted by
"Hamilton sucks" <caof@.mcmaster.cawrote in message
news:1189201676.191048.77380@.22g2000hsm.googlegrou ps.com...
Quote:
Originally Posted by
>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?
Quote:
Originally Posted by
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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns99A56D13F4F0DYazorman@.127.0.0.1...
Quote:
Originally Posted by
David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
Quote:
Originally Posted by
>"Hamilton sucks" <caof@.mcmaster.cawrote in message
>news:1189201676.191048.77380@.22g2000hsm.googlegrou ps.com...
Quote:
Originally Posted by
>>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?
>
Quote:
Originally Posted by
>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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Thanks Erland. My mistake.
--
David Portas|||On Sep 7, 6:08 pm, "David Portas"
<REMOVE_BEFORE_REPLYING_dpor...@.acm.orgwrote:
Quote:
Originally Posted by
"Hamilton sucks" <c...@.mcmaster.cawrote in message
>
news:1189201676.191048.77380@.22g2000hsm.googlegrou ps.com...
>
Quote:
Originally Posted by
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.
>
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
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...@.sommarskog.sewrote:
Quote:
Originally Posted by
David Portas (REMOVE_BEFORE_REPLYING_dpor...@.acm.org) writes:
Quote:
Originally Posted by
"Hamilton sucks" <c...@.mcmaster.cawrote in message
news:1189201676.191048.77380@.22g2000hsm.googlegrou ps.com...
Quote:
Originally Posted by
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.
>
Quote:
Originally Posted by
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?
>
Quote:
Originally Posted by
DECLARE @.loc_id VARCHAR(36);
SET @.loc_id = CAST(NEWID() AS VARCHAR(36));
>
Quote:
Originally Posted by
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.|||"Hamilton sucks" <caof@.mcmaster.cawrote in message
news:1189273920.752746.25660@.o80g2000hse.googlegro ups.com...
Quote:
Originally Posted by
>
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.
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 ... ? ;
Quote:
Originally Posted by
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.
>
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
No comments:
Post a Comment