DSXchange

Hi,

How you copy one row to multiple rows in data stage?

Thanks.

Kumar

What do you mean by "copy"?

What I meant was is there way to copy 1 row from source and duplicate it into many rows in target.

Kumar

[quote="kcbland"]What do you mean by "copy"?[/quote]

Do you mean update multiple target rows with values derived from one input row? Your question is poorly worded.

In SQL, you'd use a wildcard query. Your question, however, doesn't hint at which methods you prefer.

vskr72 wrote:What I meant was is there way to copy 1 row from source and duplicate it into many rows in target.

Kumar

kcbland wrote:What do you mean by "copy"?

Kumar,

Are you attempting to take one row with multiple columns and put each column in a separate row in the output? If so then you need to look at the Pivot stage.

What I need is Record1 in source should be duplicated to 10 records ( all Record 1) in Target. Hope it is clear on what I need. Thank you.

Kumar

[quote="shawn_ramsey"][quote="vskr72"]What I meant was is there way to copy 1 row from source and duplicate it into many rows in target.

Kumar

[quote="kcbland"]What do you mean by "copy"?[/quote][/quote]
Kumar,

Are you attempting to take one row with multiple columns and put each column in a separate row in the output? If so then you need to look at the Pivot stage.[/quote]

How do you identify the 10 records in the target? If you have the key available you could just do an update of your target table on your key.

Or you could place the value you want to be duplicated in a hash file and then have your target table pass through a transformer and perform a lookup.

Without more information, I can only answer in generalities.

vskr72 wrote:What I need is Record1 in source should be duplicated to 10 records ( all Record 1) in Target. Hope it is clear on what I need. Thank you.

Kumar

In 10 separate tables?

Kumar,

If you mean to say that you have to create 10 replications of 1 incoming record. Then first make sure that you have no keys defined in the target metadata, because you are trying to replicate here. Sorting the source data would be nice too but not necessary.

And as far as the replications are concerned, build a custom routine that takes in all the fields from the source file/table as input arguments. Concatenate all these fields together and then concatenate with a EOL character (char(10):char(13)). Repeat this code 10 times assuming you are replicating 1:10.

Call this routine into a Stage Variable and then load the target.

I think this will work fine if implemented correctly. Please let me know if you have any questions.

Thanks,
Naveen.

Transformer stage with N output links ought to do it.

Thank you Ray and Naveen. It worked.

I have a simple job which is as follows-
1. ODBC source reads from SQL Server.
2. Necessary logic perfomed in the Transformer which also gets a reference input via a Hash file.
3. Result passed to a sequential file. The resulting Seq File has over 100,000 rows. Now, I need to add a date field to the result. The date (lets call it some_date) comes from Oracle. The same date should be added to all the rows; no checking of any kind is required. How can this be done efficiently?

I had a similar situation where i had to send 1 incoming row to 7 different target oracle tables based on day of the week. If its monday then hit monday table.
In that situation i used 7 OCI stages as output. only 1 input record is being passed. I put a constraint over there. Here if you dont have a constraint then 1 single record will hit all the 7 outputs.

That's what I did too, just used an OCI stage to feed the row via transformer. It easily solves my problem, where I select the date where the key = 1 and set the key value of the OCI to '1' in the transformer. But the purpose of my question was whether it is a good idea to use reference link to feed data. Because, in my particular case, there is no need of the key value of the OCI stage.

You can't perform any kind of lookup without a reference key.