Show Navigation | Hide Navigation
You are here:
Geoprocessing tool reference > Data Management toolbox > Layers and Table Views toolset > Tools

Make Query Table (Data Management)

Release 9.2
Last modified November 29, 2010
E-mail This Topic Printable Version Give Us Feedback

Print all topics in : "Tools"


Related Topics

The Make Query Table tool applies an SQL query to a database and the results are represented in a layer or table view. The query can be used to join several tables or return a subset of columns or rows from the original data in the database.

Make Query Table accepts data from an ArcSDE geodatabase, a file geodatabase, a personal geodatabase, or an OLE DB connection.


Usage tips

Command line syntax
An overview of the Command Line window
MakeQueryTable_management <in_table;in_table...> <out_table> <USE KEY FIELDS | ADD VIRTUAL KEY FIELD | NO KEY FIELD> {in_key_field;in_key_field...} <Field {Alias};Field {Alias}...> {where_clause}

Parameter Explanation Data Type
<in_table;in_table...>

The name of the table or tables to be used in the query. If several tables are listed, the Expression parameter can be used to define how they are to be joined.

The input table can be from an ArcSDE geodatabase, a file geodatabase, a personal geodatabase or an OLE DB connection.

Table View | Raster Layer
<out_table>

The name of the layer or table view that will be created by the tool.

Table View | Raster Layer
<USE KEY FIELDS | ADD VIRTUAL KEY FIELD | NO KEY FIELD>

Indicates how an ObjectID field will be generated, if at all, for the query. The default is USE_KEY_FIELDS.

  • USE_KEY_FIELDS — This indicates that the fields chosen in the key fields list should be used to define the dynamic ObjectID column. If there are no fields chosen in the key fields list, the ADD VIRTUAL_KEY_FIELD option is automatically applied.
  • ADD_VIRTUAL_KEY_FIELD — This option indicates that no key fields have been chosen, but a dynamic ObjectID column is to be generated. This is done by copying the data to a local, system managed workspace and adding a field with unique values to the copy. The layer or table view can then access the copy and use the added field as the key field.
  • NO_KEY_FIELD — This option indicates that no dynamic ObjectID column is to be generated. Choosing this option means that selections will not be supported for the table view. If there is already a column of type ObjectID in the fields list, it will be used as the ObjectID even if this option is chosen.

String
{in_key_field;in_key_field...}

Specifies a field or combination of fields that can be used to uniquely identify a row in the query. This parameter is used only when the USE_KEY_FIELDS option is set.

The Add Field button, which is used only in ModelBuilder, allows you to add expected field(s) so you can complete the dialog and continue to build your model.

Field
<Field {Alias};Field {Alias}...>

The fields to include in the layer or table view. If an alias is set for a field, this is the name that appears. If no fields are specified, all fields from all tables are included.

The Add Field button, which is used only in ModelBuilder, allows you to add expected field(s) so you can complete the dialog and continue to build your model.

String
{where_clause}

An SQL expression used to select a subset of records.

The syntax for the expression differs slightly depending on the data source. For example, if you're querying file or ArcSDE geodatabases, shapefiles, coverages, dBASE or INFO tables, enclose field names in double quotes:

"MY_FIELD"

If you're querying personal geodatabases, enclose fields in square brackets:

[MY_FIELD].

For more information on SQL syntax and how it differs between data sources, see SQL Reference.

SQL Expression
Data types for geoprocessing tool parameters


Command line example

MakeQueryTable "Database Connections/balrog.odc/vtest.COUNTIES;Database Connections/balrog.odc/vtest.CODEMOG" vtest.COUNTIES.OBJECTID 'ObjectID' ;vtest.COUNTIES.NAME 'Name';vtest.CODEMOG.Males 'Males';vtest.CODEMOG.Females 'Females'" "vtest.COUNTIES.FIPS = vtest.CODEMOG.Fips and vtest.COUNTIES.STATE_NAME = 'California'"

Scripting syntax
About getting started with writing geoprocessing scripts
MakeQueryTable_management (in_table, out_table, in_key_field_option, in_key_field, in_field, where_clause)

Parameter Explanation Data Type
in_table (Required)

The name of the table or tables to be used in the query. If several tables are listed, the Expression parameter can be used to define how they are to be joined.

The input table can be from an ArcSDE geodatabase, a file geodatabase, a personal geodatabase or an OLE DB connection.

Table View | Raster Layer
out_table (Required)

The name of the layer or table view that will be created by the tool.

Table View | Raster Layer
in_key_field_option (Required)

Indicates how an ObjectID field will be generated, if at all, for the query. The default is USE_KEY_FIELDS.

  • USE_KEY_FIELDS — This indicates that the fields chosen in the key fields list should be used to define the dynamic ObjectID column. If there are no fields chosen in the key fields list, the ADD VIRTUAL_KEY_FIELD option is automatically applied.
  • ADD_VIRTUAL_KEY_FIELD — This option indicates that no key fields have been chosen, but a dynamic ObjectID column is to be generated. This is done by copying the data to a local, system managed workspace and adding a field with unique values to the copy. The layer or table view can then access the copy and use the added field as the key field.
  • NO_KEY_FIELD — This option indicates that no dynamic ObjectID column is to be generated. Choosing this option means that selections will not be supported for the table view. If there is already a column of type ObjectID in the fields list, it will be used as the ObjectID even if this option is chosen.

String
in_key_field (Optional)

Specifies a field or combination of fields that can be used to uniquely identify a row in the query. This parameter is used only when the USE_KEY_FIELDS option is set.

The Add Field button, which is used only in ModelBuilder, allows you to add expected field(s) so you can complete the dialog and continue to build your model.

Field
in_field (Required)

The fields to include in the layer or table view. If an alias is set for a field, this is the name that appears. If no fields are specified, all fields from all tables are included.

The Add Field button, which is used only in ModelBuilder, allows you to add expected field(s) so you can complete the dialog and continue to build your model.

String
where_clause (Optional)

An SQL expression used to select a subset of records.

The syntax for the expression differs slightly depending on the data source. For example, if you're querying file or ArcSDE geodatabases, shapefiles, coverages, dBASE or INFO tables, enclose field names in double quotes:

"MY_FIELD"

If you're querying personal geodatabases, enclose fields in square brackets:

[MY_FIELD].

For more information on SQL syntax and how it differs between data sources, see SQL Reference.

SQL Expression

Data types for geoprocessing tool parameters


Script example

# MakeQueryTableOLEDB.py
# Description: Create a query table from two OLE DB tables using a limited set of
#               fields and establishing a join.
# Author: ESRI
# Date 1/30/2004

# Create the geoprocessor object
import arcgisscripting
gp = arcgisscripting.create()

try:
    # Local variables...
    Table_list = "Database Connections/balrog.odc/vtest.COUNTIES; Database Connections/balrog.odc/vtest.CODEMOG"
    Field_list = "vtest.COUNTIES.OBJECTID 'ObjectID' ;vtest.COUNTIES.NAME 'Name';vtest.CODEMOG.Males 'Males';vtest.CODEMOG.Females 'Females'"
    where_clause = "vtest.COUNTIES.FIPS = vtest.CODEMOG.Fips and vtest.COUNTIES.STATE_NAME = 'California'"

    # Make Query Table...
    gp.MakeQueryTable(Table_list,"CountyCombined","USE_KEY_FIELDS","vtest.COUNTIES.OBJECTID",field_list,where_clause)

    # Print the total rows
    pDSC = gp.describe("CountyCombined")
    print gp.getcount("CountyCombined")

    # Print the fields
    Fields = pDSC.Fields
    Field = Fields.Next()
    while Field:
        print Field.Name
        fld = Fields.Next()

    # Save as a dBASE file
    gp.CopyRows("CountyCombined", "d:/temp/calinfo.dbf")

except:
    # If an error occurred print the message to the screen
    print gp.GetMessages()

Please visit the Feedback page to comment or give suggestions on ArcGIS Desktop Help.
Copyright © Environmental Systems Research Institute, Inc.