Dynamic Insert SQL Statement
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 298
- Joined: Tue Aug 26, 2008 12:17 pm
Dynamic Insert SQL Statement
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,
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,
Joyce A. Recacho
São Paulo/SP
Brazil
São Paulo/SP
Brazil
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,
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,
- james wiles
All generalizations are false, including this one - Mark Twain.
All generalizations are false, including this one - Mark Twain.
-
- Participant
- Posts: 298
- Joined: Tue Aug 26, 2008 12:17 pm
-
- Participant
- Posts: 298
- Joined: Tue Aug 26, 2008 12:17 pm
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 15
- Joined: Wed May 02, 2012 3:38 am
how can write dynamic sql statent
hello Caig,
please help me how to write dynamic sql statements
Adavance thanks
please help me how to write dynamic sql statements
Adavance thanks
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.
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.
"It is not the lucky ones are grateful.
There are the grateful those are happy." Francis Bacon
There are the grateful those are happy." Francis Bacon
-
- Participant
- Posts: 298
- Joined: Tue Aug 26, 2008 12:17 pm
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,
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,
Joyce A. Recacho
São Paulo/SP
Brazil
São Paulo/SP
Brazil
-
- Participant
- Posts: 298
- Joined: Tue Aug 26, 2008 12:17 pm
Ray, thanks for your help.ray.wurlod wrote: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 ...
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,
Joyce A. Recacho
São Paulo/SP
Brazil
São Paulo/SP
Brazil
-
- Participant
- Posts: 298
- Joined: Tue Aug 26, 2008 12:17 pm
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,
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,
Joyce A. Recacho
São Paulo/SP
Brazil
São Paulo/SP
Brazil
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 298
- Joined: Tue Aug 26, 2008 12:17 pm
Hi Craig.ray.wurlod wrote:You ARE able to use sequences. ...
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,
Joyce A. Recacho
São Paulo/SP
Brazil
São Paulo/SP
Brazil