Can we use both INSERT and UPDATE at the same time in JDBC Receiver

[Former Member]

Hi All,

I would like to know is it possible to use both INSERT and UPDATE at the same time in one interface because I have a requirement in which I have to perform both the task.

user send the file which contains both new and old record and I need to save those in MS SQL database.

If the record exist then use UPDATE otherwise use INSERT.

I looked on sdn but didn't find any blog which perform both the things at the same time.

Interface Requirement

FILE -

---

> PI -

---

> JDBC(INSERT & UPDATE)

I am thinking to use JDBC Lookup but not sure if it good to use for bulk record.

Can somebody please suggest me something or send me the link of any blog or anything to solve this problem.

Thanks,

[Former Member]

You can use UPDATE_INSERT. If fits the bill.

If you check below link

http://help.sap.com/saphelp_nw04/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm

Initially, the same action is executed as for UPDATE. If no update to the database table can be made for this action (the condition does not apply to any table entry), values of the table described in the <access> element are inserted in accordance with the description of the action INSERT. <key> elements are ignored in this case.

[former_member854360]

Hi,

In your scenario you can use jdbc lookup .

In mapping do a JDBC lookup to the table to find that any data already exist or not.

Use If then else standrad function to map the Action.

If Jdbc lookup return data exist then map Update other wise map Insert

[Former Member]

go for update_insert:

http://help.sap.com/saphelp_nw04/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm

there is one other i.e use Stored procedures on the target side and pass ur soure payload as input to the SP and let SP handle the update/insert operations at the data base level (chk with ur DB team)....

[Former Member]

Thanks for the reply but can you please tell me how to use UPDATE_INSERT and what are the benefits of using this.

What is the difference between INSERT AND UPDATE_INSERT

Can you please tell me bit more about how to use stored procedure.

Thanks

[Former Member]

>>What is the difference between INSERT AND UPDATE_INSERT

for Update_Insert...firstly update action is performed but in case if no conditions matches for the update then Insert action will take place.

chk this (for details description, pointed already):

http://help.sap.com/saphelp_nw04/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm

>>Can you please tell me bit more about how to use stored procedure.

/people/siva.maranani/blog/2005/05/21/jdbc-stored-procedures

chk this link which tells u when to go for SP:

[anupam_ghosh2]

Hi ,

If I have understood properly the scenario properly,you are not performing insert and update together. As you posted

"If the record exist then use UPDATE otherwise use INSERT."

Thus you are performing either an insert or an update which depends on outcome of a search if the records already exist in database or not. Obviously to search the tables you need " select * from ... where ...." query. If your query returns some results you proceed with update since this means there are some old records already in database. If your query returns no rows you proceed with "insert into tablename....." since there are no old records present in database.

Now perhaps the best method to do the searching, taking a decision to insert or update, and finally insert or update operation is to be done by a stored procedure in MS SQL database. A stored procedure is a subroutine available to applications accessing a relational database system. Here the application is PI server. If you need further help on how to write and call stored procedure in MS SQL you can look into these links

http://www.daniweb.com/web-development/databases/ms-sql/threads/146829

http://www.sqlteam.com/article/stored-procedures-parameters-inserts-and-updates

[ This part you can ignore, Since its not sure that you will face this situation

*******************************************************************************************************************************************

Still you might face some problems while your scenario runs. Lets consider this scenario, after the stored procedure searches the database it found no rows. Thus you proceed with an insert operation. If your database table is being accessed by multiple applications (or users) other than yours then it is very well possible that after the search operation completed with a null result, an insert/update operation has been performed by some other application with the same primary key. Now when you are trying to insert another row with same primary key you get an error message like "duplicate entry not possible for same primary key value". Thus you need to be careful in this respect. MS SQL has a feature called "exclusive locks ". Look into these links for more details on the subject

http://msdn.microsoft.com/en-us/library/aa213039(v=sql.80).aspx

http://www.mssqlcity.com/Articles/Adm/SQL70Locks.htm

http://www.faqs.org/docs/ppbook/r27479.htm

http://msdn.microsoft.com/en-US/library/ms187373.aspx

http://msdn.microsoft.com/en-US/library/ms173763.aspx

http://msdn.microsoft.com/en-us/library/e7z8d5hf(v=vs.80).aspx

http://mssqlserver.wordpress.com/2006/11/08/locks-in-sql/

http://www.mollerus.net/tom/blog/2008/03/using_mssqls_nolock_for_faster_queries.html

There must be other methods to avoid this problem. But the point is you need to be sure that all access to database for insert/update operations are isolated.

********************************************************************************************************************************************

]

regards

Anupam

[rajasekhar_reddy14]

you can perform either insert or update , but insert update in once action not possible but UPDATE_INSERT possible.