Pushing LOAD

Here's a slightly simpler and somewhat less powerful alternative to Monday's OPENSTRING and CROSS APPLY solution to the following problem:

"Let's say you have a table that contains a tab-delimited list of field values in a single string column, and you want to split those values apart and store them in separate columns in another table."

To make it simpler yet, let's change "tab-delimited list" to "comma-delimited" which is the default.

Here's the input table:

CREATE TABLE source_table (
   source_pkey   INTEGER NOT NULL PRIMARY KEY,
   row           LONG VARCHAR NOT NULL );

INSERT source_table VALUES ( 1, '21, ''Hello'',  99, 123.45\x0d\x0a' );
INSERT source_table VALUES ( 2, '22, ''World'',  88, 456.78\x0d\x0a' );
INSERT source_table VALUES ( 3, '23, ''Goodbye'',77, 234.56\x0d\x0a' );
COMMIT;

Each value stored in source_table.row is a string which in turn contains four field values; e.g., an integer 21, a string 'Hello', another integer 99, a decimal value 123.45 and so on. Commas separate the field values inside source_table.row, and a \x0d\x0a carriage return - line feed pair is used as row delimiter.

Here's the output table, with one column corresponding to each of the four field values stored in source_table.row:

CREATE TABLE target_table (
   target_pkey    INTEGER NOT NULL PRIMARY KEY,
   data1          VARCHAR ( 10 ) NOT NULL,
   data2          INTEGER NOT NULL,
   data3          DECIMAL ( 11, 2 ) NOT NULL );

Here's a LOAD TABLE statement that splits-and-copies the data from source_table.row into target_table:

LOAD TABLE target_table
   USING COLUMN row 
           FROM source_table 
          ORDER BY source_pkey;

The USING clause on lines 2 through 4 tells the LOAD TABLE statement that

• the data is coming from COLUMN row,

• that column is coming FROM source_table, and

• and the rows are to be loaded in ORDER BY source_pkey.

Here's what the input and output look like:



Tip: If you only see one row in target_table, you probably forgot the row delimiter (\x0d\x0a in this case).