insert statement script/stored proc.

I have a table that has 3 columns that I need to split up the data in
each column and insert into another table as rows.
As well for each record inserted the Status is set to "O".
I am thinking I stored procedure would be the way to go? But trying to
determine the best way to go about this.
Below is an example of the table structures.
I have 2 options, delete all the data from table2 and do a bunch of
inserts, or perform updates?
I am assuming the first would be better but having trouble with the sql
statement.
Any ideas?
table1
ID Name1 Name2 CITY
500 John Jeff TO
501 Sheila Rose TO
502 Barb Jen TO
503 Tom Jerry TO
504 Alan Scott TO
505 Steve John TO
506 Pat Cathy TO
table2
ID Name Status
500 John O
500 Jeff O
501 Sheila O
501 Rose O
502 Barb O
502 Jen O
503 Tom O
503 Jerry O
504 Alan O
504 Scott O
505 Steve O
505 John O
506 Pat O
506 Cathy O
One way is to use an UNION like:
SELECT id, name1 AS "Name" FROM tbl
UNION
SELECT id, name2 AS "Name" FROM tbl
Another option is to use a CASE expression like:
SELECT id, CASE seq WHEN 1 THEN name1 ELSE name 2 END
FROM tbl, ( SELECT 1 UNION SELECT 2 ) D ( seq )
Make sure you have a composite key on ( id, name ) to prevent potential
duplication of names.
Anith
|||<pisquem@.hotmail.com> wrote in message
news:1160507284.442954.181820@.i3g2000cwc.googlegro ups.com...
>I have a table that has 3 columns that I need to split up the data in
> each column and insert into another table as rows.
> As well for each record inserted the Status is set to "O".
> I am thinking I stored procedure would be the way to go? But trying to
> determine the best way to go about this.
> Below is an example of the table structures.
> I have 2 options, delete all the data from table2 and do a bunch of
> inserts, or perform updates?
> I am assuming the first would be better but having trouble with the sql
> statement.
> Any ideas?
>
> table1
> ID Name1 Name2 CITY
> 500 John Jeff TO
> 501 Sheila Rose TO
> 502 Barb Jen TO
> 503 Tom Jerry TO
> 504 Alan Scott TO
> 505 Steve John TO
> 506 Pat Cathy TO
>
> table2
> ID Name Status
> 500 John O
> 500 Jeff O
> 501 Sheila O
> 501 Rose O
> 502 Barb O
> 502 Jen O
> 503 Tom O
> 503 Jerry O
> 504 Alan O
> 504 Scott O
> 505 Steve O
> 505 John O
> 506 Pat O
> 506 Cathy O
>
I might not be following, but this should do what you wish.
INSERT table2 (ID, Name, Status)
SELECT ID, Name1, 'O'
FROM Table1
UNION ALL
SELECT ID, Name2, 'O'
FROM Table1
Rick Sawtell
|||Thanks for the posts.
Which is the most effective and efficient way?
Rick Sawtell wrote:
> <pisquem@.hotmail.com> wrote in message
> news:1160507284.442954.181820@.i3g2000cwc.googlegro ups.com...
> I might not be following, but this should do what you wish.
> INSERT table2 (ID, Name, Status)
> SELECT ID, Name1, 'O'
> FROM Table1
> UNION ALL
> SELECT ID, Name2, 'O'
> FROM Table1
>
> Rick Sawtell
|||Darn, I forgot to include something...
In the second table I need to have another column inserted with the
value of either 001 or 002 depending on if its Name1 being inserted or
Name2. So the table should be outputed as follows.
ID Name Code Status
500 John 001 O
500 Jeff 002 O
501 Sheila 001 O
501 Rose 002 O
Any ideas?
pisq...@.hotmail.com wrote:[vbcol=seagreen]
> Thanks for the posts.
> Which is the most effective and efficient way?
>
> Rick Sawtell wrote: