<<<<<<< HEAD table | H2 | SQLDoc
Skip to main content

table

1. TABLE Function

======= table | H2 Function | SQLDoc

table

1. TABLE Function

>>>>>>> 9d19b1c8cb744c8e86d5c15b74d8b5f719ad62be

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