Generated SQL statement for unused columns

sohasaid · Post by **sohasaid** » Wed Oct 10, 2012 6:39 am

I'm using DRS stage as a destination to insert or update data. The problem that it generates insert or update statement for every column at the destination table although they're not defined at job!

Example:
1- table has three columns
2- one column only is defined at the job but generated insert statement for the whole three and it puts DEFAULT for the unused columns.

INSERT INTO STG_ADDRESS
(ADDRESS_NAME,STREET_ADDRESS,STREET_ADDRESS2)
VALUES
(:ADDRESS_NAME,DEFAULT,DEFAULT)

How can I get rid of this and limit the generated SQL for the used columns only especially I'm loading tables with almost 500 columns?

chulett · Post by **chulett** » Wed Oct 10, 2012 7:27 am

Clarify for us what exactly "they're not defined at job" means. If they exist in the target stage, they will be included in the generated SQL. It doesn't work like Informatica, if you have that expectation.

sohasaid · Post by **sohasaid** » Wed Oct 10, 2012 7:49 am

chulett wrote:Clarify for us what exactly "they're not defined at job" means.

It means that those columns are not defined at DRS stage metadata.

Considering the above example, I insert data into the first column only from DataStage and the other columns are left to database to handle.

chulett · Post by **chulett** » Wed Oct 10, 2012 8:07 am

Interesting, so it is checking the actual table's metadata rather than what is defined in the stage? From the docs, there is an option of either "Fully generated SQL query or Column-generated SQL query", if you are using the former then perhaps a switch to the other is appropriate. I don't have any way to check / confirm that however.

Worst case the documentation suggests having the stage show you the generated SQL which you then copy to an editor and edit to make appropriate user-defined SQL. However, I can't imagine you won't be able to get it to do what you want.