SQL Subcommand (SAVE TRANSLATE command)

The SQL subcommand provides the ability to issue any SQL directives that are needed in the target database. It can be used, for example, to define joins or alter table properties in the database to include new columns or modify column properties.

• Each SQL statement must be enclosed in quotes.
• You can use multiple lines for a single SQL statement by using multiple quoted strings connected with plus signs (the standard string concatenation symbol).
• Each quoted line cannot exceed 256 characters.
• Multiple SQL statements can be included by using multiple SQL subcommands.
• Table and field specifications in SQL statements refer to tables and fields available in the database, not datasets and variables available in the IBM® SPSS® Statistics session (although in many cases the names may be the same).
• Regardless of the position of the SQL subcommand, the SQL statements are executed last, after all other actions executed by the SAVE TRANSLATE command.

Example: Adding New Columns to an Existing Table

SAVE TRANSLATE /TYPE=ODBC
 /CONNECT=
 'DSN=MS Access Database;DBQ=C:\examples\data\dm_demo.mdb;'
 'DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;'
  /TABLE = 'NewColumn'
  /KEEP ID income_score
  /REPLACE 
  /SQL='ALTER TABLE CombinedTable ADD COLUMN income_score REAL'
  /SQL='UPDATE CombinedTable INNER JOIN NewColumn ON ' +
  'CombinedTable.ID=NewColumn.ID SET ' +
  'CombinedTable.income_score=NewColumn.income_score'.

• The TABLE, KEEP, and REPLACE subcommands create or replace a table named NewColumn that contains two variables: a key variable (ID) and a computed score (income_score).
• The first SQL subcommand, specified on a single line, adds a column to an existing table that will contain values of the computed variable income_score. At this point, all we have done is create an empty column in the existing database table, and the fact that both database tables and the active dataset use the same name for that column is merely a convenience for simplicity and clarity.
• The second SQL subcommand, specified on multiple lines with the quoted strings concatenated with plus signs, adds the income_score values from the new table to the existing table, matching rows (cases) based on the value of the key variable ID.

The end result is that an existing table is modified to include a new column containing the values of the computed variable.

Example: Specifying Data Types for a New Table

SAVE TRANSLATE /TYPE=ODBC
  /CONNECT='DSN=MS Access Database;DBQ=c:\temp\temp.mdb;DriverId=25;FIL=MS'+
  ' Access;MaxBufferSize=2048;PageTimeout=5;'
  /TABLE=tempTable    /REPLACE
  /SQL='CREATE TABLE NewTable(numVar double, intVar integer, dollarVar currency)'
  /SQL='INSERT INTO NewTable(numVar, intVar, dollarVar) SELECT * FROM tempTable'
  /SQL='DROP TABLE tempTable'.

• The TABLE subcommand creates a new table that contains variables in the active dataset with the default database data types. In this example, the original variables have IBM SPSS Statistics variable formats of F8.2, F4.0, and Dollar10.2 respectively, but the default database type for all three is double.
• The first SQL subcommand creates another new table with data types explicitly defined for each field. At this point, this new table contains no data.
• The second SQL subcommand inserts the data from the tempTable into NewTable. This does not affect the data types previously defined for NewTable, so intVar will have a data type of integer and dollarVar will have a data type of currency.
• The last SQL subcommand deletes tempTable, since it is no longer needed.