Skip to main content

table

1. TABLE Function​

The TABLE function in H2 database is used to generate a result set from a CSV string or a file.

2. Syntax​

The syntax for the TABLE function in H2 database is as follows:

TABLE('csvString' [, 'column1', 'column2', ...])
TABLE('filePath' [, 'column1', 'column2', ...])

Arguments​

  • csvString: A string containing comma-separated values representing the data for each row.
  • filePath: The path to a CSV file containing the data.
  • column1, column2, ...: Optional column names to be used in the result set. If not provided, default column names (COLUMN0, COLUMN1, etc.) will be used.

Return​

  • The TABLE function returns a result set with the specified columns and data from the CSV string or file.

3. Notes​

  • The CSV string or file should follow the standard CSV format, with comma-separated values and line breaks indicating different rows.
  • If the CSV string or file contains a header row, it is recommended to specify column names in the function call to provide meaningful column headers in the result set.
  • The TABLE function is useful for quickly generating result sets from static data without the need for creating and populating a table.

4. Examples​

Here are a few examples demonstrating the usage of the TABLE function in H2 database:

Example 1 - Using the TABLE function with a CSV string:​

SELECT * FROM TABLE('John,Doe,30\nJane,Smith,25\nMike,Johnson,40', 'First Name', 'Last Name', 'Age');

Output:

First Name | Last Name | Age
------------+------------+-----
John       | Doe        | 30
Jane       | Smith      | 25
Mike       | Johnson    | 40

Example 2 - Using the TABLE function with a CSV file:​

Assuming we have a CSV file named 'employees.csv' with the following content:

ID,First Name,Last Name,Age
1,John,Doe,30
2,Jane,Smith,25
3,Mike,Johnson,40
SELECT * FROM TABLE('employees.csv', 'ID', 'First Name', 'Last Name', 'Age');

Output:

ID | First Name | Last Name | Age
---+------------+-----------+-----
1  | John       | Doe       | 30
2  | Jane       | Smith     | 25
3  | Mike       | Johnson   | 40
  • csvread - Read CSV file into a table
  • csvwrite - Write a table to a CSV file