Skip to main content
Question

SQL Merge Statement

  • October 9, 2015
  • 8 replies
  • 84 views
chris_rmw
Contributor
Forum|alt.badge.img+2
Hi,

 

Is it possible to run a SQL Merge Statement from within the SQL Executor?  My SQL DBA provided me with the following Merge Statement which I put in the Executor and I've also inserted the FME_SQL_Delimiter statement at the beginning because I was getting an error about semicolons being detected.

 

 

 

FME_SQL_DELMITER^ MERGE TERANET_OWNERSHIPPROPERTYREPORT AS Detail USING TXC_STAGING_OWNERSHIPPROPERTYREPORT AS Stage ON (Detail.PIN = Stage.PIN and Detail.lronumber = Stage.LRONumber and Detail.instrumentnumber = Stage.instrumentnumber and Detail.instrumenttype = Stage.instrumenttype) WHEN NOT MATCHED THEN INSERT (LRONumber, PIN, StreetName, StreetNumber, StreetSuffix, UnitTypeCode, UnitNumber, MunicipalityName, Description, OwnerName1, OwnerName2, OwnerName3, OwnerNameMore, RegistrationSystemCode, InstrumentNumber, InstrumentType, ConsiderationAmount, RegistrationDate, PartyTo1, PartyTo2, PartyTo3, PartyToMore, PartyFrom1, PartyFrom2, PartyFrom3, PartyFromMore) VALUES(Stage.lronumber, Stage.pin, Stage.streetname, Stage.streetnumber, Stage.streetsuffix, Stage.unittypecode, Stage.unitnumber, Stage.municipalityname, Stage.description, Stage.ownername1, Stage.ownername2, Stage.ownername3, Stage.ownernamemore, Stage.registrationsystemcode, Stage.instrumentnumber, Stage.instrumenttype, Stage.considerationamount, Stage.registrationdate, Stage.partyto1, Stage.partyto2, Stage.partyto3, Stage.partytomore, Stage.partyfrom1, Stage.partyfrom2, Stage.partyfrom3, Stage.partyfrommore) WHEN MATCHED AND(ISNULL(Detail.streetname,'') <> ISNULL(Stage.streetname,'') OR ISNULL(Detail.streetnumber,0) <> ISNULL(Stage.streetnumber,0) OR ISNULL(Detail.streetsuffix,'') <> ISNULL(Stage.streetsuffix,'') OR ISNULL(Detail.unittypecode,'') <> ISNULL(Stage.unittypecode,'') OR ISNULL(Detail.unitnumber,'') <> ISNULL(Stage.unitnumber,'') OR ISNULL(Detail.municipalityname,'') <> ISNULL(Stage.municipalityname,'') OR ISNULL(Detail.description,'') <> ISNULL(Stage.description,'') OR ISNULL(Detail.ownername1,'') <> ISNULL(Stage.ownername1,'') OR ISNULL(Detail.ownername2,'') <> ISNULL(Stage.ownername2,'') OR ISNULL(Detail.ownername3,'') <> ISNULL(Stage.ownername3,'') OR ISNULL(Detail.ownernamemore,'') <> ISNULL(Stage.ownernamemore,'') OR ISNULL(Detail.registrationsystemcode,'') <> ISNULL(Stage.registrationsystemcode,'') OR ISNULL(Detail.considerationamount,'') <> ISNULL(Stage.considerationamount,'') OR ISNULL(Detail.registrationdate,convert(datetime,'1900-01-01')) <> ISNULL(Stage.registrationdate,convert(datetime,'1900-01-01')) OR ISNULL(Detail.partyto1,'') <> ISNULL(Stage.partyto1,'') OR ISNULL(Detail.partyto2,'') <> ISNULL(Stage.partyto2,'') OR ISNULL(Detail.partyto3,'') <> ISNULL(Stage.partyto3,'') OR ISNULL(Detail.partytomore,'') <> ISNULL(Stage.partytomore,'') OR ISNULL(Detail.partyfrom1,'') <> ISNULL(Stage.partyfrom1,'') OR ISNULL(Detail.partyfrom2,'') <> ISNULL(Stage.partyfrom2,'') OR ISNULL(Detail.partyfrom3,'') <> ISNULL(Stage.partyfrom3,'') OR ISNULL(Detail.partyfrommore,'') <> ISNULL(Stage.partyfrommore,'') ) THEN UPDATE SET Detail.streetname = Stage.streetname, Detail.streetnumber = Stage.streetnumber, Detail.streetsuffix = Stage.streetsuffix, Detail.unittypecode = Stage.unittypecode, Detail.unitnumber = Stage.unitnumber, Detail.municipalityname = Stage.municipalityname, Detail.description = Stage.description, Detail.ownername1 = Stage.ownername1, Detail.ownername2 = Stage.ownername2, Detail.ownername3 = Stage.ownername3, Detail.ownernamemore = Stage.ownernamemore, Detail.registrationsystemcode = Stage.registrationsystemcode, Detail.considerationamount = Stage.considerationamount, Detail.registrationdate = Stage.registrationdate, Detail.partyto1 = Stage.partyto1, Detail.partyto2 = Stage.partyto2, Detail.partyto3 = Stage.partyto3, Detail.partytomore = Stage.partytomore, Detail.partyfrom1 = Stage.partyfrom1, Detail.partyfrom2 = Stage.partyfrom2, Detail.partyfrom3 = Stage.partyfrom3, Detail.partyfrommore = Stage.partyfrommore;^

 

 

Thanks,

 

Chris
This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.

8 replies

david_r
Celebrity
  • david_rCelebrity
  • October 9, 2015
Hi

 

 

Did you try? FME simply passes the SQL on to the database, so it ought to work (with some limitations).

 

 

If you got an error, could you poste the relevant section of the log here?

 

 

David
takashi
Celebrity
  • takashiCelebrity
  • October 9, 2015
In addition, note that "FME_SQL_DELIMITER" and the delimiter character have to be separated with a white space.
Why not inspect features with Visual/Data Preview and Feature/Record Information before writing them into a destination dataset?
chris_rmw
Contributor
Forum|alt.badge.img+2
  • chris_rmwContributor
  • Author
  • Contributor
  • October 9, 2015
Hi David,

 

Here is the error statement:

 

MS SQL Server (Spatial) Reader: Query failed, possibly due to a malformed statement.

 

 

Semicolons detected in SQL. May need to use FME_SQL_DELIMITER keyword to specify a delimiter. SQL is: 'FME_SQL_DELMITER ^ MERGE TERANET_OWNERSHIPPROPERTYREPORT.....

 

= Stage.partyfrommore; ^ '.  Provider error `(-2147217900) Incorrect syntax near '^'.'
takashi
Celebrity
  • takashiCelebrity
  • October 10, 2015
If you copied&pasted the message from the log directly, probably the primary reason for the error is a typo on FME_SQL_DELIMITER.

 

And it's not essential to declare a specific delimiter if you will execute just one SQL statement.
Why not inspect features with Visual/Data Preview and Feature/Record Information before writing them into a destination dataset?
david_r
Celebrity
  • david_rCelebrity
  • October 12, 2015
Just be aware that the log entry about the FME_SQL_DELIMITER is a warning, not an error. Sometimes the warning is incorrect, and you should just ignore it. For this particular case, I'd just skip defining it altogether.

 

 

David
takashi
Celebrity
  • takashiCelebrity
  • October 12, 2015
agree, but a typo for the keyword will not be allowable anyway.

 

 

specify a delimiter. SQL is: 'FME_SQL_DELMITER ^ MERGE

 

 

can't find 'I' between L and M...
Why not inspect features with Visual/Data Preview and Feature/Record Information before writing them into a destination dataset?
J
Forum|alt.badge.img+5
  • julien
  • December 2, 2015

An another solution: Use the PythonCreator or PythonCaller to execute the SQL statement.

erik_jan
Contributor
Forum|alt.badge.img+26
  • erik_janContributor
  • Contributor
  • December 2, 2015

FME_SQL_DELIMITER needs an argument (what delimiter is used). But as it is just one SQL statement you do not need the delimiter. Just remove the semicolon at the end.

Community Stats

32,778
Posts
124,274
Replies
41,324
Members
Terms & ConditionsAccessibility statement
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.