Skip to main content
Question

SQL Merge Statement

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

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
Influencer
  • takashiInfluencer
  • October 9, 2015
In addition, note that "FME_SQL_DELIMITER" and the delimiter character have to be separated with a white space.
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
Influencer
  • takashiInfluencer
  • 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.
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
Influencer
  • takashiInfluencer
  • 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...
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+18
  • 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.

Reply


Helpful Members This Week

Community Stats

31,341
Posts
118,851
Replies
38,930
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings
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.