Write INSERT or UPDATE sql statements

One Star

Write INSERT or UPDATE sql statements

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
Seven Stars

Re: Write INSERT or UPDATE sql statements

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.
One Star

Re: Write INSERT or UPDATE sql statements

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

Re: Write INSERT or UPDATE sql statements

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.
One Star

Re: Write INSERT or UPDATE sql statements

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
Seven Stars

Re: Write INSERT or UPDATE sql statements

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

Re: Write INSERT or UPDATE sql statements

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

Re: Write INSERT or UPDATE sql statements

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.
Seven Stars

Re: Write INSERT or UPDATE sql statements

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) + "'"
One Star

Re: Write INSERT or UPDATE sql statements

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) + "'"
Seven Stars

Re: Write INSERT or UPDATE sql statements

You have the single- and double-quotes the wrong way round after context.parama. The single-quote must be inside the double-quoted string. My careless mistake above...
One Star

Re: Write INSERT or UPDATE sql statements

"Exception in component tMSSqlInput_1
java.sql.SQLException: Conversion failed when converting datetime from character string."
This eror comes when i execute the talend job and the parameters are string,if they are passed as date the job runs with success.
The code in tmssqlinput_1 (parama/b are string if they are dates no issue)
"select * from fn_sas_ext(
'" + TalendDate.parseDate("yyyy-MM-dd",context.parama) +" '
,
'" + TalendDate.parseDate("yyyy-MM-dd",context.paramb) +" '
)"
Please guide
Seven Stars

Re: Write INSERT or UPDATE sql statements

Why are you now using parseDate?! If parama and paramb are strings (in the correct yyyy-MM-dd format) then just use them directly to construct the SQL statement.
One Star

Re: Write INSERT or UPDATE sql statements

Thanks Alevy ,i have to take dates from the user input as dd/mm/yyyy and then pass the dates to mssqlinput in (mm/dd/yyyy) as this format is default for ms sqlserver ,i have taken string as input but it gives error
"java.lang.RuntimeException: java.text.ParseException: Unparseable date: "01-02-2011""
please guide, thanks.
Seven Stars

Re: Write INSERT or UPDATE sql statements

If it's stating 'Unparseable date: "01-02-2011"' then it's clearly not in dd/mm/yyyy format.
You need to understand what you're trying to do, which is to construct a SQL statement that looks the same as if you were using it directly in SQL Server.
To that end, if you have a String date, it must be in the correct format to append to the rest of the SELECT statement. If it is not in the correct format, you need to parse it to a Date (using it's actual format) and then format it back to a String in the correct format. Obviously, if you already have a Date you only need to format it to a String in the correct format.
e.g. '" + TalendDate.formatDate("yyyy-MM-dd",TalendDate.parseDate("dd/MM/yyyy",context.parama)) + "'
One Star

Re: Write INSERT or UPDATE sql statements

Alevy sir , I want to pass input parameters to toraclesp without using tfixedflowinput, i have declared two context variables as paramA,paramB.Please guide,thanks in advance.
One Star

Re: Write INSERT or UPDATE sql statements

one more issue ,is oracle global temporary table no use in talend as the data cannot be inserted in another table in the same job,e.g i have a table tbl_stag which is GTT and in the same job through a procedure i am trying to insert into another table but no rows inserted into normal table.please guide.