Use a variation of the INSERT statement and a global temporary table to rearrange the import data to fit the table.
To create a global temporary table, you must have one of the following system privileges:
The privileges required to import (load) data depend on the settings of the -gl database option, as well as the source of the data you are importing from. See the LOAD TABLE statement for more information about the privileges required to load data.
To use the INSERT statement, you must be the owner of the table or have one of the following privileges:
Additionally, if the ON EXISTING UPDATE clause is specified, you must have the UPDATE ANY TABLE system privilege or UPDATE privilege on the table.
In the SQL Statements pane, create a global temporary table with a structure matching that of the input file.
You can use the CREATE TABLE statement to create the global temporary table.
Use the LOAD TABLE statement to load your data into the global temporary table.
When you close the database connection, the data in the global temporary table disappears. However, the table definition remains. You can use it the next time you connect to the database.
Use the INSERT statement with a SELECT clause to extract and summarize data from the temporary table and copy the data into one or more permanent database tables.
The following is an example of the steps outline above.
CREATE GLOBAL TEMPORARY TABLE TempProducts ( ID integer NOT NULL, Name char(15) NOT NULL, Description char(30) NOT NULL, Size char(18) NOT NULL, Color char(18) NOT NULL, Quantity integer NOT NULL, UnitPrice numeric(15,2) NOT NULL, CONSTRAINT ProductsKey PRIMARY KEY (ID) ) ON COMMIT PRESERVE ROWS; LOAD TABLE TempProducts FROM 'C:\\ServerTemp\\newProducts.csv' SKIP 1; INSERT INTO Products WITH AUTO NAME (SELECT Name, Description, ID, Size, Color, Quantity, UnitPrice * 1.25 AS UnitPrice FROM TempProducts);
The price of the items in the global temporary table are adjusted upwards by 25% before inserting the rows into the Products table.
Discuss this page in DocCommentXchange.
|Copyright © 2014, SAP AG or an SAP affiliate company. - SAP Sybase SQL Anywhere 16.0|