Add records in a table

[Hamilton sucks]

Hi:
I need to add some records in a table called location(primar y 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]

Re: Add records in a table

"Hamilton sucks" <caof@mcmaster. cawrote in message
news:1189201676 .191048.77380@2 2g2000hsm.googl egroups.com...

Hi:
I need to add some records in a table called location(primar y 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]

Re: Add records in a table

David Portas (REMOVE_BEFORE_ REPLYING_dporta s@acm.org) writes:

"Hamilton sucks" <caof@mcmaster. cawrote in message
news:1189201676 .191048.77380@2 2g2000hsm.googl egroups.com...

>I need to add some records in a table called location(primar y 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@sommarsk og.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

[David Portas]

Re: Add records in a table

"Erland Sommarskog" <esquel@sommars kog.sewrote in message
news:Xns99A56D1 3F4F0DYazorman@ 127.0.0.1...

David Portas (REMOVE_BEFORE_ REPLYING_dporta s@acm.org) writes:

>"Hamilton sucks" <caof@mcmaster. cawrote in message
>news:118920167 6.191048.77380@ 22g2000hsm.goog legroups.com...

>>I need to add some records in a table called location(primar y 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@sommarsk og.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/prodinf...ons/books.mspx

Thanks Erland. My mistake.

--
David Portas

[Hamilton sucks]

Re: Add records in a table

On Sep 7, 6:08 pm, "David Portas"
<REMOVE_BEFORE_ REPLYING_dpor.. .@acm.orgwrote:

"Hamilton sucks" <c...@mcmaster. cawrote in message
>
news:1189201676 .191048.77380@2 2g2000hsm.googl egroups.com...
>

Hi:
I need to add some records in a table called location(primar y 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]

Re: Add records in a table

On Sep 8, 4:38 am, Erland Sommarskog <esq...@sommars kog.sewrote:

David Portas (REMOVE_BEFORE_ REPLYING_dpor.. .@acm.org) writes:

"Hamilton sucks" <c...@mcmaster. cawrote in message
news:1189201676 .191048.77380@2 2g2000hsm.googl egroups.com...

I need to add some records in a table called location(primar y 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...@sommarsk og.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.c om/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.c om/sql/prodinfo/previousversion s/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]

Re: Add records in a table

"Hamilton sucks" <caof@mcmaster. cawrote in message
news:1189273920 .752746.25660@o 80g2000hse.goog legroups.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