topic Re: Append Tool ignores SQL expression in ArcGIS Pro Questions

Append Tool ignores SQL expression

FranziskaSumpf — Fri, 08 May 2026 09:16:47 GMT

Hi, I'm using the append tool to update some data. I have entered an sql expression to filter the input data, but the tool completely ignored it and updated all records!

In one case the expression was "NAME <> ' '", in another "SHORTNAME IS NOT NULL". Both resulted in records being overwritten with blanks and nulls, respectively.

I'm also using a field map and a matching field.

Feature classes are in different geodatabases, editor tracking is active on the target feature class. Using AGP 3.6.2

Also: It would be great if there was an update tool, without the insert, or an option within the append tool to disable insert (though the tool name would be misleading in that case). I keep deleting the inserted rows because I only want the update.

Re: Append Tool ignores SQL expression

Robert_LeClair — Fri, 08 May 2026 15:46:05 GMT

Your filter may not be excluding what you think (spaces vs NULLs). SHORTNAME IS NOT NULL only excludes NULL values. It will not exclude empty spaces or strings that are just spaces. NAME <> ' ' excludes exactly one space and will still allow an empty string, two spaces, etc. So your source features can still pass the filter and then append blank/NULL values into the target, overwriting existing values. Try filtering with something that excludes both NULL and "blank-ish" strings. For example:

SHORTNAME IS NOT NULL AND TRIM(SHORTNAME) <> '' or NAME IS NOT NULL AND TRIM (NAME) <> ''

There is a BUG-000157944 for ArcGIS Pro that was fixed at 3.7 that may be related as well.

Re: Append Tool ignores SQL expression

KevinSaavedra — Fri, 08 May 2026 17:54:25 GMT

I'm able to reproduce this with a simple SQL Query (is not null) and matching field for update. As a potential workaround, can you try Select Layer By Attribute on the input data and use that selection instead of the SQL query in the Append tool?

Re: Append Tool ignores SQL expression

FranziskaSumpf — Mon, 11 May 2026 06:31:42 GMT

My SQL queries are correct. One field has a lot of nulls, on those weren't filtered out. Another field has a lot of blanks, just singe spaces (I used the drop down in the expression builder), and those weren't filtered out either. And this were different runs of the tool, combining both expressions.

The bug does not seem to apply, either. I have used the tool quite a lot recently, always with a field map, and it worked like a charm (except when I need an sql query). I also don't know if the field map makes a difference in my case. The data just happens do be very chaotic with lot of different field names for the same thing.

Re: Append Tool ignores SQL expression

RhettZufelt — Mon, 11 May 2026 14:52:12 GMT

Have you tried it without the matching field option?

Since you say you are deleting the appended rows, I assume they are not initially in the target dataset.
I'm wondering if you SQL expression is working, but, the matching field option is then 'updating' the filtered out rows since they are 'missing' in the target.

If this is the case, testing without the matching field option would not append the blank/null value rows.

Re: Append Tool ignores SQL expression

FranziskaSumpf — Tue, 12 May 2026 08:15:50 GMT

Interesting idea. But the appended rows are not those I filter out with the sql. What you describe is a different scenario than mine.

There are indeed records missing in the target data, because I ran the Delete Identical GP tool (they were identical except for the FIXED_ID that I had added in an earlier step). So of course I don't want to reinsert those duplicates, but I need to update the other records.

To understand what I am doing: I have point features in about 90 different feature classes, with different schemas, and containing duplicates (i. e. some points from one feature have duplicates in one or several other feature classes). I have to make heads and tails of the data and check them against our own database. In a first step, I merged all feature classes into one, keeping only fields that seemed important, and then I manually reduced the number of fields further (e. g. fields named "BEZ", "Name", Name_", "Bezeichn" etc. all mean the same thing, so I merged them into just one field). I had also added a continuous ID across all feature classes, and a field with the name of the feature class, so I can trace everything back to the original source data. I then ran the Delete Identical GP tool to get rid of duplicates, before checking the points against our own data. While checking, I sometimes find that some data did not come across in the merge. In order to get these information into the merged feature class, I use the Append GP tool, but what I actually need is just the Update. I usually don't need an sql query to filter the source data, but sometimes the source data itself is inconsistent, and half of the features have the relevant information in one field (e.g. "SHORTNAME"), and the other half in another (e.g. "NAME"). I tried to concatenate in the field map, but this did not work (I think it was because SHORTNAME is null when it's not filled in).

Sure, there are multiple workarounds I can use, but the issue remains, that the Append tool just ignores an input parameter. To me, that is a bug, and it should be fixed.