Staging table

[urshit_1983]

Hi All,

I have to create staging table. That has to be done by entering custom SQL which is a bit complex as it has many joins. Can any body suggest how shall I proceed ?

Database is Oracle.

Can I do it by TOAD or DS ?

Thanks.

[meena]

Hi urshit_1983
How many tables you have to join? I think u can do it in DS in the oracle database stage. let us know what kind of join you want to do and also the exact scenario....Then we can help you...

Thank you

[urshit_1983]

Join is not a problem other parameters are to be checked. Its like 3 to 4 tables. And I need a staging table to store this. So my question is , is DS required for this purpose or i can do it in TOAD ? To build staging table...

Thanks

[Krazykoolrohit]

I have to create staging table.

To create the table use DDLs, run it on database. no need of datastage.

Even to populate the table, use oracle if you have no major transformations to be done on the data and you just need to make some joins and populate the staging table.

[urshit_1983]

Ok so Data stage does not come in the picture then. So the steps:

Create Table using TOAD
write required SQL
populate the table with required columns

Right ??

[Krazykoolrohit]

In case you are obsessed on datastage create stored proc and call it from a datastage job or sequence

[meena]

Hi urshit_1983,
Sorry, The way I took the question was different.I can suggest you the same as Krazykoolrohit..

Join is not a problem other parameters are to be checked. Its like 3 to 4 tables. And I need a staging table to store this. So my question is , is DS required for this purpose or i can do it in TOAD ? To build staging table...

Thanks

Thank you

[Krazykoolrohit]

Ok so Data stage does not come in the picture then.

Not untill you have some transformations to be done on data. If you are just populating the tables, datastage may become an overhead for you.

[urshit_1983]

The only thing is complex SQL. Either I use DS or TOAD I have to write custom SQL. So I am asking is DS necessary or TOAD should be fine for the same ?

Or else make a table using TOAD and then use DS to load it ???

[narasimha]

Like already discussed above
If your purpose is to create a staging table, without any transformations, you could do it using TOAD.

Create a table with the required coulmns.

Insert the results of your query into the table using

Code: Select all

Insert into <table-name> 
(
 <Your complex query>.
) 

The number of coulmns/datatype of columns in your table has to match the number of columns/datatype of your query output

[chulett]

Or simply:

Code: Select all

CREATE TABLE AS
(your query)

Two birds, one stone. DataStage is perfectly capable of creating any table you have the grants to create in TOAD. As to if you should use DataStage, that depends somewhat on what you are accomplishing here. A one time 'staging' of data? No, you're fine via TOAD. Something you'll need to 'stage' on a regular basis? Then yes.

[urshit_1983]

Thankyou everybody let me try that.