Add records in a table

[Hamilton sucks]

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.

[David Portas]

"Hamilton sucks" <ca**@mcmaster.cawrote in message
news:11*********************@22g2000hsm.googlegrou ps.com...

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

[Erland Sommarskog]

David Portas (RE****************************@acm.org) writes:

"Hamilton sucks" <ca**@mcmaster.cawrote in message
news:11*********************@22g2000hsm.googlegrou ps.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, es****@sommarskog.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

[David Portas]

"Erland Sommarskog" <es****@sommarskog.sewrote in message
news:Xn**********************@127.0.0.1...

David Portas (RE****************************@acm.org) writes:

>"Hamilton sucks" <ca**@mcmaster.cawrote in message
news:11*********************@22g2000hsm.googlegro ups.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, es****@sommarskog.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

Thanks Erland. My mistake.

--
David Portas

[Hamilton sucks]

On Sep 7, 6:08 pm, "David Portas"
<[e-mail]:

"Hamilton sucks" <[e-mail] in message

news:11*********************@22g2000hsm.googlegrou ps.com...

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.

[Hamilton sucks]

On Sep 8, 4:38 am, Erland Sommarskog <[e-mail]:

David Portas ([e-mail]) writes:

"Hamilton sucks" <[e-mail] in message
news:11*********************@22g2000hsm.googlegrou ps.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, [e-mail]

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.

[David Portas]

"Hamilton sucks" <ca**@mcmaster.cawrote in message
news:11*********************@o80g2000hse.googlegro ups.com...

>
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 ... ? ;

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