OdbcOutput Update and Insert

Post Reply
mnasra
Posts: 136
Joined: Tue Aug 10, 2010 5:40 pm
OLAP Product: Planning Analytics
Version: 2.0
Excel Version: EXCEL 2013

OdbcOutput Update and Insert

Post by mnasra »

Hi Expert,

I know how to write the sql for odbcoutput Insert (if I truncated the table).
I know also how to write the Update, if I dont want to truncate the table.

My problem, is that the second one (which I want to use, because I dont want to truncate the table) does not do the inserts for new records.
The TI does not even give me an error when a new record is found in tm1.

what is the way to do both in the TI process.
thanks
Thanks
Micheline
declanr
MVP
Posts: 1815
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: OdbcOutput Update and Insert

Post by declanr »

Hi,

There will be many different ways to do this and it is partially dependent on the DB you are using (e.g. Oracle, SQL Server etc.)
That being said, generally, in cases like this, I would do a straight Insert/Bulk Update to a temp table in the DB and then use a MERGE to transfer data from the temp table into the actual target table.

Thanks,
Declan
Declan Rodger
mnasra
Posts: 136
Joined: Tue Aug 10, 2010 5:40 pm
OLAP Product: Planning Analytics
Version: 2.0
Excel Version: EXCEL 2013

Re: OdbcOutput Update and Insert

Post by mnasra »

It is a SQL Server table.
And yes, it is exactly what usually we do. I appreciate the answer, because the more techy than me are giving all sorts of SQL commands, and none is working.

Thank you for your help, I will stop looking and do exactly what you said.
Thanks
Micheline
Post Reply