Write INSERT or UPDATE sql statements

[Anonymous]

I have a file I need to process. Based on a value in the file, I need to check in the database if it finds based on the key, then generate an UPDATE statement with values from the current row in the file, otherwise INSERT statement with values from the current row in the file.
the output files can be two different (for example inserts.sql and updates.sql) files. I am doing this using sqlite. I am thinking of creating the process like this.
FileInput-->SQLiteInput-->MAP-->FileOutputARFF
would this work?
Thanks,
Ravi

[alevy]

I would think the simplest approach is just to use FileInput to tMap with tSQLiteInput as lookup where you can construct the appropriate SQL statement and then output using tFileOutputDelimited.

[Anonymous]

Thanks for the response. How do I pass parameters to tSqlInput?

[alevy]

What sort of parameters do you want to pass? The SQL input statement is just a String that can be built up by concatenating hard-coded Strings and variables e.g. http://www.talendforge.org/forum/viewtopic.php?pid=61516#p61516.

[Anonymous]

hmm! I am not finding a good example of the process how to accomplish this.
for example, i have a file that has two columns, and multiple rows.
id, desc
------------------
1 sample1
2 samepl2
database table has two columns with Id as PK
---------------------------------------------------
id, desc
------------------
1 old sample1

now, the file has two entries, and database has one entry
I start processing the file, pick up first record, query it in database (select * from table where id = <id from file>), if finds, it I need to write update statement (UPDATE table set desc = <desc from file> WHERE id= <id from file>) otherwise it need to write INSERT statement (INSERT INTO table (id, desc) values (<id from file>, <desc from file>) into two separate files or one file.
/IF FOUND, write UPDATE statement as above to a file
/
file -> dbquery (pass id as input param) /
\
\
\NOT FOUND, write INSERT statement as above to a file

Thanks

[alevy]

tFileInputDelimited --main--> tMap --> tFileOutputDelimited
^
|
lookup
|
tSQLLiteInput (SELECT ID FROM Table)

[Anonymous]

Thanks alevy, that worked like a champ! that's what I needed, struggled a bit.

[Anonymous]

i want to pass two parameters to MSsqlinput (ie from_date,to_date) how ?
my code below
and outstanding_from_date >=" + TalendDate.formatDate("yyyy-MM-dd",context.parama)
" and outstanding_to_date <= " + TalendDate.formatDate("yyyy-MM-dd",context.paramb)
this gave error"delete this token,can you please guide,thanks in advance.

[alevy]

The error is because you're missing the + needed to concatenate the string containing the second part of your condition. But you also haven't included the single quotes that SQL Server uses to delimit dates:

and outstanding_from_date >='"  + TalendDate.formatDate("yyyy-MM-dd",context.parama) + '"
and outstanding_to_date <= '" + TalendDate.formatDate("yyyy-MM-dd",context.paramb) + "'"

[Anonymous]

Thanks alevy but still error'invalid character constant' ,the below sql in tMSSQlInput ,pls guide.
"select
count(1)
from pf_account a ,pf_os b where
a.perform_id = b.Perform_id
and (a.gl_account_id not like '080%' or
a.gl_account_id not in (select gl_account_id from pf_gl_code_exclusion))
and outstanding_from_date >='" + TalendDate.formatDate("yyyy-MM-dd",context.parama) + '"
and outstanding_to_date <= '" + TalendDate.formatDate("yyyy-MM-dd",context.paramb) + "'"