Page 1 of 1
Appending values from two columns into one
Posted: Mon Jun 05, 2006 4:57 pm
by narasimha
I need to take values from 2 columns from a table and put in into another table as a single column
Example
Code: Select all
I have 2 columns in table X with values
A B
---- ----
1 32
1 8
2 0
43 1
0 2
34 1
I need to insert it into another table Y's column C as
I could run the insert job twice, pulling in different column at a time.
But I was wondering if i could do this in one job or if there was a better approach to this?
Posted: Mon Jun 05, 2006 5:36 pm
by ray.wurlod
Your source extraction could be user-defined SQL.
Code: Select all
SELECT A FROM tablename UNION ALL SELECT B FROM tablename;
Naturally this requires that columns A and B have compatible data types but, then, so does inserting them into the one column in your target. If they are incompatible, use CAST to guarantee that they are in the result of the query.
Posted: Tue Jun 06, 2006 9:16 am
by narasimha
Thanks Ray,
Your's is a better appraoch, It is better to do a union all, rather than running the job twice
Posted: Tue Jun 06, 2006 10:09 am
by deployDS
You could also use a funnel stage. Use a copy stage to send the table data into 2 streams.
Col A would be renamed to Col C in one stream and Col B would be renamed to Col C in other stream. You can funnel them and take Col C as Col A and col B data together.
Again the same metadata assumption holds.
Posted: Tue Jun 06, 2006 10:10 am
by DSguru2B
The OP sure can but too bad the funnel stage is only available in PX and not server
Posted: Tue Jun 06, 2006 10:10 am
by chulett
Not in a Server job.
Posted: Tue Jun 06, 2006 10:11 am
by DSguru2B
Beat you to it Craig
Posted: Tue Jun 06, 2006 3:20 pm
by ray.wurlod
Unless, of course, one were to write one's own server Funnel stage...
Posted: Wed Jun 07, 2006 3:49 pm
by dsdesigner
ray.wurlod wrote:Unless, of course, one were to write one's own server Funnel stage...
...
Can't we use the Link Collector stage to achieve the functionality of a funnel stage?
dsdesigner
Posted: Wed Jun 07, 2006 4:37 pm
by ray.wurlod
Yes and no. There isn't a collection method in the Link Collector stage that permits "all from source A then all from source B". The (parallel) Funnel stage has a "Sequence" method that permits this mode of operation.
Posted: Wed Jun 07, 2006 4:41 pm
by dsdesigner
ray.wurlod wrote:Yes and no. There isn't a collection method in the Link Collector stage that permits "all from source A then all from source B". The (parallel) Funnel stage has a "Sequence" method that permits this ...
Ooops! Misread the requirement. Will work on that. Thanks