Copy one row to multiple rows
Moderators: chulett, rschirm, roy
Copy one row to multiple rows
Hi,
How you copy one row to multiple rows in data stage?
Thanks.
Kumar
How you copy one row to multiple rows in data stage?
Thanks.
Kumar
Copy one row to multiple rows
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]
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.
In SQL, you'd use a wildcard query. Your question, however, doesn't hint at which methods you prefer.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
-
- Participant
- Posts: 145
- Joined: Fri May 02, 2003 9:59 am
- Location: Seattle, Washington. USA
Re: Copy one row to multiple rows
Kumar,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"?
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.
Shawn Ramsey
"It is a mistake to think you can solve any major problems just with potatoes."
-- Douglas Adams
"It is a mistake to think you can solve any major problems just with potatoes."
-- Douglas Adams
Re: Copy one row to multiple rows
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]
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]
-
- Participant
- Posts: 437
- Joined: Fri Oct 21, 2005 10:00 pm
Re: Copy one row to multiple rows
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.
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.
Keith Williams
keith@peacefieldinc.com
keith@peacefieldinc.com
-
- Participant
- Posts: 145
- Joined: Fri May 02, 2003 9:59 am
- Location: Seattle, Washington. USA
Re: Copy one row to multiple rows
In 10 separate tables?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
Shawn Ramsey
"It is a mistake to think you can solve any major problems just with potatoes."
-- Douglas Adams
"It is a mistake to think you can solve any major problems just with potatoes."
-- Douglas Adams
-
- Premium Member
- Posts: 1255
- Joined: Wed Feb 02, 2005 11:54 am
- Location: United States of America
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.
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Re: Copy one row to multiple rows
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?
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.
Code: Select all
ODBC ==> Transformer ==> Seq File
^
||
Hash File
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.
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.
Code: Select all
OCI
||
V
ODBC ==> Transformer ==> Seq File
^
||
Hash File
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: