trying to read records in one table to create new records in another

[sparks]

build new records 1 to many
test values in source and if it's a 1 in source it's a name in
destination

source table destination table
row 1 col1=1 row 1 col1=bob
row 1 col2=1 row 2 col1=fred
row 1 col3=1 row 3 col1=carl

build multible records based on what is in the source rows.

do for all rows in source.

I can see doing this by reading each column and saying if col1 =1 then
destination.col1 = bob

being new to this I do not know the correct way to automate this.

any pointers are greatly appreciated

[Bob Barrows]

Post by sparks
build new records 1 to many
test values in source and if it's a 1 in source it's a name in
destination
source table destination table
row 1 col1=1 row 1 col1=bob
row 1 col2=1 row 2 col1=fred
row 1 col3=1 row 3 col1=carl
build multible records based on what is in the source rows.
do for all rows in source.
I can see doing this by reading each column and saying if col1 =1 then
destination.col1 = bob
being new to this I do not know the correct way to automate this.
any pointers are greatly appreciated

Given the unrealistic sample data all I can suggest is a union query, which
is the typical way of folding data:

select col1 as col1 from [source table]
union all
select col2 from [source table]
union all
select col3 from [source table]

How you translate "1" to "bob", "fred" or "carl" in the union's select
statements is up to you, along with the extra maintenance you'll be doing by
writing the data to another table instead of simply creating a View from the
union query.

[sparks]

Thanks
that got me going in the right direction.
got it done.



On Thu, 14 Jun 2012 10:40:29 -0400, "Bob Barrows"

Post by Bob Barrows

Post by sparks
build new records 1 to many
test values in source and if it's a 1 in source it's a name in
destination
source table destination table
row 1 col1=1 row 1 col1=bob
row 1 col2=1 row 2 col1=fred
row 1 col3=1 row 3 col1=carl
build multible records based on what is in the source rows.
do for all rows in source.
I can see doing this by reading each column and saying if col1 =1 then
destination.col1 = bob
being new to this I do not know the correct way to automate this.
any pointers are greatly appreciated

Given the unrealistic sample data all I can suggest is a union query, which
select col1 as col1 from [source table]
union all
select col2 from [source table]
union all
select col3 from [source table]
How you translate "1" to "bob", "fred" or "carl" in the union's select
statements is up to you, along with the extra maintenance you'll be doing by
writing the data to another table instead of simply creating a View from the
union query.