Adding New Fields

To add new fields to an existing database table:

  1. From the menus in the Data Editor window for the dataset that you want to use to add fields to the database table, choose:

    File > Export to Database

  2. Select the database source. See the topic Selecting a Data Source for more information.
  3. In the Choose how to export the data panel of the Export to Database Wizard, select Add new fields to an existing table.
  4. In the Select a table or view panel, select the database table. See the topic Selecting a Table for more information.
  5. In the Match cases to records panel, match the variables that uniquely identify each case to the corresponding database field names. See the topic Matching Cases to Records for more information.
  6. Drag and drop the variables that you want to add as new fields to the Source of values column.

For information on field names and data types, see the section on creating database fields from IBM® SPSS® Statistics variables in Exporting to a Database.

Field name. The default field names are the same as the IBM SPSS Statistics variable names. You can change the field names to any names allowed by the database format. For example, many databases allow characters in field names that aren't allowed in variable names, including spaces. Therefore, a variable name like CallWaiting could be changed to the field name Call Waiting.

Type. The export wizard makes initial data type assignments based on the standard ODBC data types or data types allowed by the selected database format that most closely matches the defined IBM SPSS Statistics data format--but databases can make type distinctions that have no direct equivalent in IBM SPSS Statistics, and vice versa. For example, most numeric values in IBM SPSS Statistics are stored as double-precision floating-point values, whereas database numeric data types include float (double), integer, real, and so on. In addition, many databases don't have equivalents to IBM SPSS Statistics time formats. You can change the data type to any type available in the drop-down list.

As a general rule, the basic data type (string or numeric) for the variable should match the basic data type of the database field. If there is a data type mismatch that cannot be resolved by the database, an error results and no data are exported to the database. For example, if you export a string variable to a database field with a numeric data type, an error will result if any values of the string variable contain non-numeric characters.

Width. You can change the defined width for string (char, varchar) field types. Numeric field widths are defined by the data type.

Value Labels. If a variable has defined variable labels, export value label text instead of values. For values that do not have a defined value label, the data value is exported as a text string. This option is not available for date format variables or variables that do not have any defined value labels.

Show existing fields. Select this option to display a list of existing fields. You cannot use this panel in the Export to Database Wizard to replace existing fields, but it may be helpful to know what fields are already present in the table. If you want to replace the values of existing fields, see Replacing Values in Existing Fields.

User-Missing Values

There are two options for the treatment of user-missing values when data from variables are exported to database fields:

  • Export as valid values. User-missing values are treated as regular, valid, nonmissing values.
  • Export numeric user-missing as nulls and export string user-missing values as blank spaces. Numeric user-missing values are treated the same as system-missing values. String user-missing values are converted to blank spaces (strings cannot be system-missing).