Appending values from two columns into one

[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

Code: Select all

C
----
1
1
2
43
34
32
8
1
2
1

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?

[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.

[narasimha]

Thanks Ray,

Your's is a better appraoch, It is better to do a union all, rather than running the job twice

[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.

[DSguru2B]

The OP sure can but too bad the funnel stage is only available in PX and not server

[chulett]

Not in a Server job.

[DSguru2B]

Beat you to it Craig

[ray.wurlod]

Unless, of course, one were to write one's own server Funnel stage...

[dsdesigner]

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

[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.

[dsdesigner]

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