Wednesday, November 24, 2010

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).

No comments: