Considerations and Limitations

The map all clause of the create compareset has several considerations and limitations.

  • When using the map all statement in create compareset, the source and target table columns must have identical names.

  • When the source table contains one or more identity columns, these columns are mapped as DA compareset key columns, and DA does not look for primary keys or unique indexes.

  • When the source table does not have any identity columns, but has one or more primary-key columns, these columns are mapped as DA compareset key columns. DA does not look for unique indexes.

  • When the source table does not have any identity or primary key columns, but has one or more unique indexes, the indexed columns are mapped as compareset key columns from the first unique clustered or first unique nonclustered index DA finds.

  • If no identity, primary key, or unique indexed columns are found, the create compareset command fails.

  • The map all statement never maps non-key Adaptive Server timestamp columns.
Limitations for Adaptive Server:
  • If your table does not have a primary key or identity column, enforcing a primary key using sp_primarykey stored procedure does not enable map all to work. For example, map all cannot map a table defined as:
    CREATE TABLE orders (
    order_num INTEGER NOT NULL,
    date_ordered DATE,
    name CHAR(80)
  )
  sp_primarykey orders, order_num
go
    The map all clause cannot be used here because a table with a primary key defined using the sp_primarykey system procedure lacks a primary-key integrity constraint. You can create an index to enforce a primary-key integrity constraint. For example:
      
create unique clustered index ordernumidx on orders (order_num)
go
    You can verify that a table has a primary-key constraint by using the sp_helpconstraint system procedure. See Specifying Unique and Primary Key Constraints in the Adaptive Server Enterprise Transact-SQL Users Guide.
Parent topic: create compareset