copy-options

INCLUDING IDENTITY COLUMN ATTRIBUTES or EXCLUDING IDENTITY COLUMN ATTRIBUTES
Specifies whether identity column attributes are inherited.
INCLUDING IDENTITY COLUMN ATTRIBUTES
Specifies that the table inherits the identity attribute, if any, of the columns resulting from select-statement, table-name, or view-name. In general, the identity attribute is copied if the element of the corresponding column in the table, view, or select-statement is the name of a table column or the name of a view column that directly or indirectly maps to the name of a base table column with the identity attribute. If the INCLUDING IDENTITY COLUMN ATTRIBUTES clause is specified with the AS select-statement clause, the columns of the new table do not inherit the identity attribute in the following cases:
  • The select list of the select-statement includes multiple instances of an identity column name (that is, selecting the same column more than once).
  • The select list of the select-statement includes multiple identity columns (that is, it involves a join).
  • The identity column is included in an expression in the select list.
  • The select-statement includes a set operation (UNION or INTERSECT).

If INCLUDING IDENTITY is not specified, the table will not have an identity column.

EXCLUDING IDENTITY COLUMN ATTRIBUTES
Specifies that the table does not inherit the identity attribute, if any, of the columns resulting from the fullselect, table-name, or view-name.
EXCLUDING COLUMN DEFAULTS or INCLUDING COLUMN DEFAULTS or USING TYPE DEFAULTS
Specifies whether column defaults are inherited.
EXCLUDING COLUMN DEFAULTS
Specifies that the column defaults are not inherited from the definition of the source table. The default values of the column of the new table are either null or there are no default values. If the column can be null, the default is the null value. If the column cannot be null, there is no default value, and an error occurs if a value is not provided for a column on INSERT for the new table.
INCLUDING COLUMN DEFAULTS
Specifies that the table inherits the default values of the columns resulting from the select-statement, table-name, or view-name. A default value is the value assigned to a column when a value is not specified on an INSERT.

Do not specify INCLUDING COLUMN DEFAULTS, if you specify USING TYPE DEFAULTS.

If INCLUDING COLUMN DEFAULTS is not specified, the default values are not inherited.

USING TYPE DEFAULTS
Specifies that the default values for the table depend on the data type of the columns that result from the select-statement, table-name, or view-name. If the column is nullable, then the default value is the null value. Otherwise, the default value is as follows:
Data type Default value
Numeric 0
Fixed-length character or graphic string Blanks
Fixed-length binary string Hexadecimal zeros
Varying-length string A string length of 0
Date The current date at the time of INSERT
Time The current time at the time of INSERT
Timestamp The current timestamp at the time of INSERT
Datalink A value corresponding to DLVALUE('','URL','')
distinct-type The default value of the corresponding source type of the distinct type.

Do not specify USING TYPE DEFAULTS if INCLUDING COLUMN DEFAULTS is specified.