Creating a New Table or Replacing a Table

To create a new database table or replace an existing database table:

  1. From the menus in the Data Editor window for the dataset that you want to use to create 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 wizard, select Drop an existing table and create a new table of the same name or select Create a new table and enter a name for the new table. If the table name contains any characters other than letters, numbers, or an underscore, the name must be enclosed in double quotes.
  4. If you are replacing an existing table, in the Select a table or view panel, select the database table. See the topic Selecting a Table for more information.
  5. Drag and drop variables into the Variables to save column.
  6. Optionally, you can designate variables/fields that define the primary key, change field names, and change the data type.

Primary key. To designate variables as the primary key in the database table, select the box in the column identified with the key icon.

  • All values of the primary key must be unique or an error will result.
  • If you select a single variable as the primary key, every record (case) must have a unique value for that variable.
  • If you select multiple variables as the primary key, this defines a composite primary key, and the combination of values for the selected variables must be unique for each case.

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.

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).