Dynamic Insert SQL Statement

[joycerecacho]

Hello Guys, I need some help.

Imagine the following file content:

TABLE_NAME;COLUMN;VALUE
PRODUCT;'PRODUCT_COD';'999'
PRODUCT;'PRODUCT_NAME'; 'ANY NAME'
PRODUCT;'QUANTITY';10
PRODUCT;'INPUT_DATE';'2012-01-01'
ADDRESS;'STREET_NAME';'ANY NAME'
ADDRESS;'ZIPCODE';'999999999'

DataStage must to generate the following INSERT Statements from this file:

INSERT INTO PRODUCT (PRODUCT_COD, PRODUCT_NAME, QUANTITY, INPUT_DATE) VALUES ('999','ANY NAME',10,'2012-01-01')

INSERT INTO ADDRESS (STREET_NAME, ZIPCODE) VALUES ('ANY_NAME','999999999')

It's possible to appear at the next day, for example, a content like:

TABLE_NAME;COLUMN;VALUE
PRODUCT;'PRODUCT_COD';'999'
PRODUCT;'PRODUCT_NAME'; 'ANY NAME'
PRODUCT;'QUANTITY';10
PRODUCT;'INPUT_DATE';'2012-01-01'
ADDRESS;'STREET_NAME';'ANY NAME'
ADDRESS;'ZIPCODE';'999999999'
CUSTOMER;'CUSTOMER_NAME';'JOHN'

And we also have to insert into a third table with a different structure. In this case, it has only one column as follow:

INSERT INTO CUSTOMER (CUSTOMER_NAME) VALUES ('JOHN')

IN SHORT: Can DataStage create dynamic SQL Statements for each record based on a file content like this above?
Create the SQL Statement and execute it, of course.
Waiting different tables with different number of columns.

Thanks for your help, my friends.

Best Regards,

[jwiles]

I imagine not. A database stage/connector will write to one table.

You can load into multiple tables by using multiple database stages, but each stage should receive only the data destined for it's assigned table.

Regards,

[joycerecacho]

The idea is to generate different SQL Statements based on the register.
Thanks for your help!!!

Best regards,

[chulett]

Server or Parallel job? You marked it as one and posted in the other's forum.

[joycerecacho]

Parallel, Craig.
Tks!

What if I create a File with all SQL Statements that must be executed and inside the ODBC stage tell it to base its Statement at this file?
Is it possible to execute one by one? Does it understand?

Best regards,

[ray.wurlod]

I have done this in the past, but not all in one job. I had a server job build the custom SQL and place it into its user status area. A downstream parallel job receives the entire SQL statement as a job parameter from activity variable Job1.$UserStatus, and uses this in the appropriate stage in Job2. Both jobs are, needless to say, executed from the same sequence.

[chulett]

It seems to me that the problem of building dynamic SQL like that would be much easier to solve in a Server job.

[venkateshrupineni]

hello Caig,

please help me how to write dynamic sql statements

Adavance thanks

[BI-RMA]

Hello Joyce,

if you want to insert your data into existing database tables, you need to know the structure of these tables beforehand. So you could design a fixed Interface for each of these tables and split your stream according to the table name contained in your file.

Does your file contain values for a single product, address, customer only (You do not mention any key-values to distinguish where one product ends or the next one begins)?

Have a StageVariable for each table-column in a transformer with:
svProductCod = If DSLink1.COLUMN = 'PRODUCT_COD' then DSLink1.VALUE Else svProductCod
and so on.

Send the values to the tables when LastRow() is reached. This way there is no need for dynamic SQL and everything is handled in a single (parallel) job.

[joycerecacho]

Roland, thanks for your help.

Actually I can't split the data cause today the file can have data to insert into 3 tables and tomorrow, for example, there are data to insert into 5 tables. It's dynamic. The tables have different structures and I don't know them. Got it?

I know the ID which identifies the table where data must be inserted in, the columns and their contents. These example I mentioned it's unreal. Yes, I can identify the register - when it starts and when it finishes.

When I worked with Server I remember I could create a file with the SQL Statement and the Database Stage (Oracle, ODBC, etc) could read it instead of writing the Query. I'll make some tests, I don't know if it's possible through parallel jobs.

Tks Guys.

Best Regards,

[joycerecacho]

I have done this in the past, but not all in one job. I had a server job build the custom SQL and place it into its user status area. A downstream parallel job receives the entire SQL statement as a ...

Ray, thanks for your help.
When you say: "...and place it into its user status area", How Can I direct the Query I created at the Job1 to this Activity Variable?


Tks!!
Best Regards,

[chulett]

Do an exact search for "DSSetUserStatus".

[joycerecacho]

Tks Craig, I'll try to understand how to use it.
What if I am not able to use Sequences?
Is there any other way? - cause I've already noticed that they don't usually use Sequences at the bank. I don't know whether it is allowed or not. I think not.


In this case, our pattern is to develop only Parallel jobs and the stage used is ODBC cause the tables are SQL Server....

Best Regards,

[ray.wurlod]

You ARE able to use sequences.

[joycerecacho]

You ARE able to use sequences. ...

Hi Craig.
Actually I asked my customer If I am able to use sequences because of their Rules, you know?
Also...
They only have Parallel Jobs there and I don't know If I can create Servers.

Anyway, I need to know how to set a value to a parameter set.
It would be inside a transformer?
PS. This value is from a column of a file.

I've been searching about it...........
Best Regards,