solidDB Help : Configuring and administering : Using solidDB data management tools : solidDB Speed Loader (solloado and solload) : Examples of solidDB Speed Loader usage
  
Examples of solidDB Speed Loader usage
Loading fixed-format records
In fixed-length format import files, data records have a fixed length and the data fields inside the records have a fixed position and length.
Example: Control file 1
This example uses multiple columns in fixed-width field:
OPTIONS(ARRAYSIZE=3)
LOAD
INFILE 'test1.dat'
INTO TABLE SLTEST
(
   "NAME"  POSITION(1-5),
   ADDRESS POSITION(6:10),
   ID      POSITION(11-15)
)
Example: Control file 2
OPTIONS (SKIP = 10, ERRORS = 5)
-- Skip the first ten records. Stop if
-- error count reaches five.
LOAD DATA
INFILE 'sample.dat'
-- import file is named sample.dat
INTO TABLE TEST1 (
IDINTEGER POSITION(1-5),
ANOTHER_ID INTEGER POSITION(8-15),
DATE1     POSITION(20:29) DATE 'YYYY-MM-DD',
DATE2   POSITION(40:49) DATE 'YYYY-MM-DD' NULLIF NULL)
Loading variable-length records
This section contains examples of the control file when loading data from a variable-length import file:
Example: Control file 3
This example uses multiple columns that have separators instead of fixed-length fields.
LOAD
INFILE 'test1.dat'
INTO TABLE SLTEST
FIELDS TERMINATED BY ','
(
   NAME,
   ADDRESS,
   ID
)
Example: Control file 4
LOAD DATA
INFILE 'EXAMP2.DAT'
INTO TABLE SUPPLIERS
FIELDS TERMINATED BY ','
(NAME VARCHAR, ADDRESS VARCHAR, ID INTEGER)
-- EXAMPLE 2
OPTIONS (SKIP=10, ERRORS=5)
-- Skip the first ten records. Stop if
-- error count reaches five.
LOAD
DATE 'YYYY-MM-DD HH:NN:SS'
-- The date format in the import file
INFILE 'sample.dat'
-- The import file
INTO TABLE TEST1
-- data is inserted into table named TEST1
FIELDS TERMINATED BY X'2C'
-- Field terminator is HEX ',' == 2C
-- This line could also be:
-- FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '[' AND ')'
-- Fields can be enclosed
-- with '[' and ')'
(
ID INTEGER,
ANOTHER_ID DECIMAL(2),
DATE1 DATE(20) DATE 'YYYY-MM-DD HH:NN:SS',
DATE2 NULLIF NULL
)
-- ID is inserted as integer
-- ANOTHER_ID is a decimal number with 2
-- digits.
-- DATE1 is inserted using the date string
-- shown
-- The default date string is used for DATE2.
-- If the column for DATE2 is 'NULL' a NULL is
-- inserted.
Running a sample load by using solidDB Speed Loader
The solidDB package contains a sample that demonstrates how to use solload to load files, see solidDB tools sample.
Go up to
solidDB Speed Loader (solloado and solload)