SAVE TRANSLATEcan also write data back to an existing database. You can create new database tables or replace or modify existing ones. As with reading database tables, writing back to a database uses ODBC, so you need to have the necessary ODBC database drivers installed.
The command syntax for writing back to a database is fairly simple, but just like reading data from a database, you need the somewhat crypticCONNECTstring. The easiest way to get the CONNECTstring is to use the Export to Database wizard (File menu in the Data Editor window, Export to Database), and then paste the generated command syntax at the last step of the wizard.
For more information on ODBC drivers andCONNECTstrings, see “Getting Data from Databases” on p. 19 in Chapter 3.
Example: Create a New Database Table
This example reads a table from an Access database, creates a subset of cases and variables, and then writes a new table to the database containing that subset of data.
*write_to_access.sps.
GET DATA /TYPE=ODBC /CONNECT=
'DSN=MS Access Database;DBQ=C:\examples\data\dm_demo.mdb;'+
'DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;' /SQL = 'SELECT * FROM CombinedTable'.
EXECUTE.
TheCONNECTstring in theSAVE TRANSLATEcommand is exactly the same as the one used in theGET DATAcommand, and thatCONNECTstring was obtained by pasting command syntax from the Database Wizard.TYPE=ODBCindicates that the data will be saved in a database.
The database must already exist; you cannot useSAVE TRANSLATEto create a database.
TheTABLEsubcommand specifies the name of the database table. If the table does not already exist in the database, it will be added to the database.
If a table with the name specified on theTABLEsubcommand already exists, theREPLACE subcommand specifies that this table should be overwritten.
You can useAPPENDinstead ofREPLACEto append data to an existing table, but there must be an exact match between variable and field names and corresponding data types. The table can contain more fields than variables being written to the table, but every variable must have a matching field in the database table.
UNSELECTED=RETAINspecifies that any filtered, but not deleted, cases should be included in the table. This is the default. To exclude filtered cases, useUNSELECTED=DELETE.
TheMAPsubcommand provides a summary of the data written to the database. In this example, we deleted all but the first three variables and first 50 cases before writing back to the database, and the output displayed by theMAPsubcommand indicates that three variables and 50 cases were written to the database.
Data written to CombinedSubset.
3 variables and 50 cases written.
Variable: ID Type: Number Width: 11 Dec: 0
Variable: AGE Type: Number Width: 8 Dec: 2
Variable: MARITALSTATUS Type: Number Width: 8 Dec: 2
Example: Append New Columns to a Database Table
TheSQLsubcommand provides the ability to issue any SQL directives that are needed in the target database. For example, theAPPENDsubcommand only appends rows to an existing table.
If you want to append columns to an existing table, you can do so using SQL directives with theSQLsubcommand.
*append_to_table.sps.
GET DATA /TYPE=ODBC /CONNECT=
'DSN=MS Access Database;DBQ=C:\examples\data\dm_demo.mdb;'+
'DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;'
/SQL = 'SELECT * FROM CombinedTable'.
/SQL='ALTER TABLE CombinedTable ADD COLUMN income_rank REAL' /SQL='UPDATE CombinedTable INNER JOIN NewColumn ON ' + 'CombinedTable.ID=NewColumn.ID SET ' +
'CombinedTable.income_rank=NewColumn.income_rank'.
TheTABLE,KEEP, andREPLACEsubcommands create or replace a table named NewColumn that contains two variables: a key variable (ID) and a calculated variable (income_rank).
The firstSQLsubcommand, specified on a single line, adds a column to an existing table that will contain values of the computed variable income_rank. 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 secondSQLsubcommand, specified on multiple lines with the quoted strings concatenated with plus signs, adds the income_rank 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 and Primary Keys for a New Table
TheTABLEsubcommand creates a database table with default database types. This example demonstrates how to create (or replace) a table with specific data types and primary keys.
*write_db_key.sps DATA LIST LIST /
ID (F3) numVar (f8.2) intVar (f3) dollarVar (dollar12.2).
BEGIN DATA
/SQL='CREATE TABLE NewTable(ID counter, numVar double, intVar smallint,'+
' dollarVar currency, primary key(ID))' /REPLACE
/TABLE='tempTable'
/SQL='INSERT INTO NewTable(ID, numVar, intVar, dollarVar) '+
' SELECT ID, numVar, intVar, dollarVar FROM tempTable' /SQL='DROP TABLE tempTable'.
The firstSQLsubcommand creates a new table with data types explicitly defined for each field and also specifies that ID is the primary key. For compound primary keys, simply include all the variables that define the primary key in parentheses afterprimary key, as in:primary key (idVar1, idVar2). At this point, this new table contains no data.
TheTABLEsubcommand creates another new table that contains variables in the active dataset with the default database data types. In this example, the original variables have variable formats of F3, F8.2, F3, and Dollar12.2 respectively, but the default database type for all four is double.
The secondSQLsubcommand inserts the data from tempTable into NewTable. This does not affect the data types or primary key designation previously defined for NewTable, so intVar will have a data type of integer, dollarVar will have a data type of currency, and ID will be designated as the primary key.
The lastSQLsubcommand deletes tempTable, since it is no longer needed.
You can use the same basic method to replace an existing table with a table that contains specified database types and primary key attributes. Just add aSQLsubcommand that specifiesDROP TABLEprior to theSQLsubcommand that specifiesCREATE TABLE.