1. Sivaprakash BKR
  2. PowerBuilder
  3. Friday, 21 September 2018

Hello,

Need to execute 'update .... ' statement, when data found while 'inserting data...', from datawindow

PB 2017, Postgresql 10.

Giving facility to import records from XML and Excel.  While importing records, few records might already available in database and need to be either excluded or updated.   This query is available in Postgres.

Can we use this 

INSERT ... ON CONFLICT DO NOTHING/UPDATE

Support for UPSERT functionality which allows either no action to be taken when a conflict is encountered, or convert the INSERT to an UPDATE.

facility from datawindow ?

https://www.postgresql.org/about/featurematrix/detail/275/

Happiness Always
BKR Sivaprakash

 

Michael Kramer Accepted Answer Pending Moderation
0
Votes
Undo

Many (most?) modern SQL dialects include the MERGE statement for this purpose:

  • I have used: MS-SQL, SAP ASE, Oracle PL/SQL, and SQL Anywhere (Watcom-SQL)
  • Verified online: IBM DB/2 for Linux/UNIX/Windows, PostgreSQL and MySQL

I import to staging table using DataWindow. Then MERGE statement in a stored procedure to transfer into app table.  Performs brilliantly because no data traffic on network moving from staging table into app tables.

HTH /Michael

Comment
There are no comments made yet.
  1. Tuesday, 31 December 2019
  2. PowerBuilder
  3. # 1
michael zuntag Accepted Answer Pending Moderation
0
Votes
Undo

I will be facing a similar situation. 

Was thinking about using a Datapipeline, using its Refresh, i think, property for dealing with existing rows. 

Just a thought. 

Mikael Hannes

Comment
There are no comments made yet.
  1. Tuesday, 31 December 2019
  2. PowerBuilder
  3. # 2
Kevin Ridley Accepted Answer Pending Moderation
0
Votes
Undo

You can always do a loop through the dw and do a Select on the key values.  If they are found, mark the row as DataModified!, otherwise NewModified!, then call Update.

Comment
Thanks Kevin Ridley.

Yes, it works. Sometimes we need to import records ranging from 5000 to 25000 numbers. it's time consuming to check all records first and then to update. Should find other [faster] ways to import.

  1. Sivaprakash BKR
  2. Thursday, 27 September 2018
You can try a stored proc or some ETL tool instead that is more geared toward that, or maybe just run overnight batch.
  1. Kevin Ridley
  2. Thursday, 27 September 2018
There are no comments made yet.
  1. Wednesday, 26 September 2018
  2. PowerBuilder
  3. # 3
Sivaprakash BKR Accepted Answer Pending Moderation
0
Votes
Undo

Seems this facility is not available.   Is there any workaround that I can insert or update (if record found in db) from records in a datawindow ?

 

Happiness Always
BKR Sivaprakash

 

Comment
There are no comments made yet.
  1. Monday, 24 September 2018
  2. PowerBuilder
  3. # 4
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.