DSXchange

I want create a table and load the data from another table on the same database. Hence I am planning to run SQL query as below. Which stage or method should I use to perform this? Please suggest.

Create table Table1 as (Select * from Table2);

Toad.

You don't need a job to do that.

IF this is a one time operation, craig got it.

But assuming that you want to schedule this, just build a simple job like this:

1-import table definition of table2

2- Build this:

connector------->transformer-------->connector

3- On connectors, load table2 definition.

4- On source connector, set generate SQL to yes, then write table2 on table name

4- On target connector, write table1 on table name, and change table action to create

5- on transformer there is a automatic correpondency button, just press it.

6- compile, run.

7- after this just change target table action to truncate and recompile.

This can be done in five minutes and i dont think there is a faster way.

Keep in mind, though, that would take all of the records from the first table and shoot them across the network to the engine and then back across to the same instance it came from to load the table. There are ways to emulate the original "do all the work in the database" technique that the original post showed and something I do in other tools when I really don't want or need all that "back and forth".

Think about doing something to get that SQL into your target connector then have a source that generates zero records. In Oracle I would do something like "select 1 from DUAL where 1=2". Your target can be any table set to almost any action because you're not going to send any records to it. Then you can put your SQL from the original post in whatever passes for the "before or after SQL" portion of the connector. Job starts, connections are established, before SQL runs, job processes zero records and is done.

Me, I'd still just use Toad. Or have a DBA run it. They love doing stuff like that.

Oracle's SQL Developer lets you right-click and select TABLE and COPY to quickly copy one table to a new table. There is a check-box for if you want data.

I just tested and it seemed to be reasonable speed-wise?

john3

If you want to automate it in DataStage and still keep all the data transfer in Oracle (for best performance):

1) Create a quick stored procedure that will handle it in Oracle and then
2) Use a stored Procedure stage in DataStage to kick it off when you need it.

The stored procedure can be parameterized to accept file names for flexibility.

asorrell wrote:The stored procedure can be parameterized to accept file names for flexibility.

Table names... and so can the Before SQL.

If there was a stage called SQL in Datastage, that would have been more awesome for this requirement. However I have implemented Craig's solution of running SQL in Before/After SQL section with zero source records. It worked. thanks!