Skip to main content
Solved

SQL Executor errors when using an insert into statement

  • October 15, 2019
  • 9 replies
  • 165 views
dbryantgeo
Contributor
Forum|alt.badge.img+7

I have the following SQL being used by a SQL Executor;

 

FME_SQL_DELIMITER ;

DROP TABLE dbo.[PREMISE_CONTRACT_ROLLBACK];

SELECT * INTO dbo.[PREMISE_CONTRACT_ROLLBACK] FROM dbo.[PREMISE_CONTRACT];

 

It runs successfully using the "Run" option when I'm accessing the transformer itself. However, if I just add a Creator > SQL Executor > Logger and run the workbench, the following error occurs;

Microsoft SQL Server Non-Spatial Reader: Query failed, possibly due to a malformed statement.  Query Text `SELECT * INTO #fme_tempAdoDataTypeTable_spatialReader FROM ( SELECT * INTO dbo.[PREMISE_CONTRACT_ROLLBACK] FROM dbo.[PREMISE_CONTRACT] ) AS customQuery WHERE 1=0'. Provider error `(-2147217900) Incorrect syntax near the keyword 'INTO'.'

Microsoft SQL Server Non-Spatial Reader: Getting Custom SQL schema failed. Make sure all columns are named (any ambiguous columns, e.g. GEOM.STBuffer(1), must have an AS clause). If there is an ORDER BY clause, either remove it or add a TOP clause

It looks like FME is wrapping the statement in its own statement and causing the syntax issue, but I don't appear to be able to get around this.

My only thoughts right now are to add a reader and a writer, but the process is already long enough without reading unnecessary data into memory.

Best answer by erik_jan

In Oracle the Insert SQL statement is:

Insert into dbo.[PREMISE_CONTRACT_ROLLBACK] Select * FROM dbo.[PREMISE_CONTRACT].

From the documentation it seems the same in SQL Server SQL:

INSERT INTO table (column1, column2, ... ) SELECT expression1, expression2, ... FROM source_table
View original
Did this help you find an answer to your question?

9 replies

erik_jan
Contributor
Forum|alt.badge.img+17
  • erik_janContributor
  • Contributor
  • October 15, 2019

Maybe not the issue, but I think the last ; should not be there.

; is used to separate two SQL statements and none is following the last ;

david_r
Evangelist
  • david_rEvangelist
  • October 16, 2019

Maybe try using a different SQL delimiter, that has worked for me previously:

FME_SQL_DELIMITER #
DROP TABLE dbo.[PREMISE_CONTRACT_ROLLBACK]
#
SELECT * INTO dbo.[PREMISE_CONTRACT_ROLLBACK] FROM dbo.[PREMISE_CONTRACT]
dbryantgeo
Contributor
Forum|alt.badge.img+7
  • dbryantgeoContributor
  • Author
  • Contributor
  • October 16, 2019

Thank you @david_r and @erik_jan. I have attempted both your proposals but unfortunately neither has worked - I'm still getting a malformed SQL error message.

david_r
Evangelist
  • david_rEvangelist
  • October 16, 2019
dbryantgeo wrote:

Thank you @david_r and @erik_jan. I have attempted both your proposals but unfortunately neither has worked - I'm still getting a malformed SQL error message.

Which version of FME are you running? The method I posted below worked fine in FME 2017.

dbryantgeo
Contributor
Forum|alt.badge.img+7
  • dbryantgeoContributor
  • Author
  • Contributor
  • October 16, 2019
david_r wrote:

Which version of FME are you running? The method I posted below worked fine in FME 2017.

Hi David, we're running 2019.1 - this is something I absolutely should've specified sorry

david_r
Evangelist
  • david_rEvangelist
  • October 16, 2019
dbryantgeo wrote:

Hi David, we're running 2019.1 - this is something I absolutely should've specified sorry

It can well be that something has changed since FME 2017, in your place I would contact Safe support about it. Perhaps also link them back to this thread.

erik_jan
Contributor
Forum|alt.badge.img+17
  • erik_janContributor
  • Contributor
  • Best Answer
  • October 16, 2019

In Oracle the Insert SQL statement is:

Insert into dbo.[PREMISE_CONTRACT_ROLLBACK] Select * FROM dbo.[PREMISE_CONTRACT].

From the documentation it seems the same in SQL Server SQL:

INSERT INTO table (column1, column2, ... ) SELECT expression1, expression2, ... FROM source_table
dbryantgeo
Contributor
Forum|alt.badge.img+7
  • dbryantgeoContributor
  • Author
  • Contributor
  • October 17, 2019
erik_jan wrote:

In Oracle the Insert SQL statement is:

Insert into dbo.[PREMISE_CONTRACT_ROLLBACK] Select * FROM dbo.[PREMISE_CONTRACT].

From the documentation it seems the same in SQL Server SQL:

INSERT INTO table (column1, column2, ... ) SELECT expression1, expression2, ... FROM source_table

How strange! At least this worked

steveatsafe
Safer
Forum|alt.badge.img+12
  • steveatsafeSafer
  • Safer
  • December 17, 2019

In my testing... the table was still created... and it seems the messages (similar to yours) were just warnings. Odd. I'll see if I can get this cleared up. Thanks for sharing.

SteveatSafe

Reply


Helpful Members This Week

Community Stats

30,886
Posts
117,069
Replies
38,698
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.