Advanced SQL: Insert select from {internal entity}

Solved

Advanced SQL: Insert select from {internal entity}

Question

Hi guys,

I have a table that maps which services are available in which zip/postal codes. (This table is a child table of the master zip code and service tables) One of the specs for this application is the ability to clone a service's zip code maping from one service to another. This obviously means a large number of records (40,000 +) need to be created everytime we clone a service. It seems like it's far more efficient to let the database do the copy in a single bulk transaction instead of using an aggreagte with a for each loop and 40,000 + individual inserts. I have an advanced query that does the bulk insert, but Outsystems keeps telling me there is an "Incorrect Syntax near the keyword INSERT". I've coppied the executed SQL code into Microsoft SQL management studio and it says the syntax is fine. If i remove the insert portion of the statement and just run the rest of the select statement, it runs just fine and returns the correct results. I'm struggling to figure out what syntax is actually incorrect. I've also noticed that when i click the "Test" button on the advanced query window the below statement does not return an error, however, when executed from the application it returns the incorrect syntax error.

The ZipCodes_ServiceMapping table only has 4 columns (Id (autonumbered), ZipCodeId, ServiceTypeID, IsSubContracted)

INSERT INTO {ZipCodes_ServiceMapping} 
            ({ZipCodes_ServiceMapping}.[ZipCodeId],
             {ZipCodes_ServiceMapping}.[ServiceTypeID],
             {ZipCodes_ServiceMapping}.[IsSubContracted])

SELECT  t1.[ZipCodeId],
        @CopyToID,
        t1.[IsSubContracted]

FROM    {ZipCodes_ServiceMapping} t1

WHERE   t1.[ServiceTypeID] = @CopyFromID and 
        NOT EXISTS (Select  t2.[ZipCodeId], 
                            t2.[ServiceTypeID]
                    FROM    {ZipCodes_ServiceMapping} t2
                    WHERE   t2.[ServiceTypeID] = @CopyToID and
                            t1.[ZipCodeId] = t2.[ZipCodeId])

AdvancedQueryScreenshot.PNG

on 24th Feb

Kadu Borges

Solution

Hi Matt Finley,

As far as I can tell, the syntax is correct. Have you tried to delete the entire query and rewrite it? Maybe there is a bad character hidden or something like that.

on 24th Feb

Dorine Boudry

Hi Matt,

so there is no error message or warning in service studio, but only once you actually execute from the running application?

Can you verify that the correct parameters are both passed in ? I don't know how you code looks, but if you have just created the new service in the database right before, are you taking the id coming back from that create action to pass into your bulk insert sql ?

Also can you show the actual error?

Dorine

on 24th Feb

5 replies

Last reply on 24th Feb

Show thread

Hide thread

Matt Finley

correct, service studio does not show any errors or warnings, even when i use the test button. Only when the query is executed from the running application, will it throw the below error message.

Error executing query. Error in advanced query CloneMapping in misc_ZipCodeCloneServiceMap in Commerce_Backend_CS (INSERT INTO {ZipCodes_ServiceMapping} ( {ZipCodes_ServiceMapping}.[ZipCodeId], {ZipCodes_ServiceMapping}.[ServiceTypeID], {ZipCodes_ServiceMapping}.[IsSubContracted] ) (SELECT t1.[ZipCodeId], @CopyToID, t1.[IsSubContracted] FROM {ZipCodes_ServiceMapping} t1 WHERE t1.[ServiceTypeID] = @CopyFromID and NOT EXISTS (Select t2.[ZipCodeId], t2.[ServiceTypeID] FROM {ZipCodes_ServiceMapping} t2 WHERE t2.[ServiceTypeID] = @CopyToID and t1.[ZipCodeId] = t2.[ZipCodeId]))): Incorrect syntax near the keyword 'INSERT'.

on 24th Feb

João Marques

mvp_badge

MVP

Replying to Matt Finley's comment on 24 Feb 2021 19:13:19

Hi Matt,

Can you try:

INSERT INTO {ZipCodes_ServiceMapping} 
            ([ZipCodeId],
             [ServiceTypeID],
             [IsSubContracted])

SELECT  t1.[ZipCodeId],
        @CopyToID,
        t1.[IsSubContracted]

FROM    {ZipCodes_ServiceMapping} t1

WHERE   t1.[ServiceTypeID] = @CopyFromID and 
        NOT EXISTS (SELECT  1
                    FROM    {ZipCodes_ServiceMapping} t2
                    WHERE   t2.[ServiceTypeID] = @CopyToID and
                            t1.[ZipCodeId] = t2.[ZipCodeId])

First, you don't need the table before the field in the fields inside the INSERT INTO and for the NOT EXISTS, you just need to have SELECT 1, you don't need to specify more than that.

Kind Regards,
João

on 24th Feb

Eduardo Jauch

mvp_badge

MVP

Replying to Matt Finley's comment on 24 Feb 2021 19:13:19

Hello

I am not sure, but I remember something about the query being "minified" before being sent to the database (everything in a single line) in Runtime that caused runtime errors when you had, for example, inline comments, that did not happen when testing from ServiceStudio because it did not minify in this context.

I do not see any inline comments on your query, though.

Cheers

on 24th Feb

Matt Finley

Replying to Eduardo Jauch's comment on 24 Feb 2021 20:14:50

Thanks for all the help guys, the issue is resolved, but i can't really explain why. I tried all the suggestions above, but still kept getting the same error. Kadu Borges suggested deleting the query and re-creating it, which I also did, but it didn't fix the issue. It did however it give me an idea on something else to try. Just for the "heck of it" i created an enterly new server action with the exact same flow and query and it works just fine. All i can assume is that something got corrupted behind the scenes with the original server action that was causing the query to fail during runtime...

on 24th Feb

Tiago Pinho

Replying to Matt Finley's comment on 24 Feb 2021 19:13:19

By looking at your error trace, it seems there is a parenthesis before the SELECT to insert:

(INSERT INTO {ZipCodes_ServiceMapping} ( {ZipCodes_ServiceMapping}.[ZipCodeId], {ZipCodes_ServiceMapping}.[ServiceTypeID], {ZipCodes_ServiceMapping}.[IsSubContracted] ) (SELECT t1.[ZipCodeId], @CopyToID, t1.[IsSubContracted] FROM {ZipCodes_ServiceMapping} t1 WHERE t1.[ServiceTypeID] = @CopyFromID and NOT EXISTS (Select t2.[ZipCodeId], t2.[ServiceTypeID] FROM {ZipCodes_ServiceMapping} t2 WHERE t2.[ServiceTypeID] = @CopyToID and t1.[ZipCodeId] = t2.[ZipCodeId]))): Incorrect syntax near the keyword 'INSERT'

on 24th Feb

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.

See the full guidelines

Training

Documentation

Advanced SQL: Insert select from {internal entity}