AnsweredAssumed Answered

How can I create tables in ArcMap using SQL and/or Scripting?

Question asked by ChipBrownHBG on Apr 19, 2018
Latest reply on Apr 24, 2018 by ChipBrownHBG

I am interested in creating a queried table/report within ArcMap using SQL and/or a scripting language (python, perhaps). Can anyone suggest an approach or resources to get this done? Eventually, I'd like to have this as part of a model.


Here is what I can do:

I can create a table using SQL in Microsoft SQL Server Management Studio of an enterprise database.  Here is a query that works in Management Studio:

select substring(MAP_ZONING, 1, 3) as ZONING, sum((Shape_Area/43560)) as Acres, COUNT(MAP_ZONING) as Frequency
from [EnterpriseDatabase]
where MAP_ZONING is not null and ExLandUse = 'VACANT'
group by substring(MAP_ZONING, 1, 3)
order by ZONING


Note: I need only the first three characters of the values in the MAP_ZONING, thus the "substring" function.


Here is what I'd like to do:

Create a similar table, but querying a feature class that is in a file geodatabase not an enterprise geodatabase on a server.


I have tried the MakeQueryTable geoprocessing tool, but there are limitations I can't seem to work around.  One is that the "Query Builder" GUI seems to only like selecting all records after a where clause, and will not accept a query like the one above.  I have tried to figure out if I can put my query in a subquery to get it to work, but have had no success.


I think this could be done using a python script, but I am a beginner and need to find some examples and resources.