Question

SQL Merge Statement

  • 9 October 2015
  • 8 replies
  • 8 views
chris_rmw
Rank
Badge +1
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
Rank
Userlevel 4
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
Rank
Userlevel 2
Badge +17
In addition, note that "FME_SQL_DELIMITER" and the delimiter character have to be separated with a white space.
chris_rmw
Rank
Badge +1
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
Rank
Userlevel 2
Badge +17
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
Rank
Userlevel 4
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
Rank
Userlevel 2
Badge +17
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
Badge +5

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

erik_jan
Rank
Userlevel 2
Badge +16

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


Community Stats

28,729
Posts
109,527
Replies
36,565
Members
Terms & ConditionsCookie 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.