Possible to INSERT with no rows in VALUES?

Question

I know this question may be a bit esoteric, but is it anyway possible to create an INSERT statement with a VALUES clause that has no values?

INSERT myTable
(col1, col2)
VALUES
<no values>

I am generating the INSERT statements programmatically and I can solve this by having an IF check that will not generate the INSERT, but I am curious this VALUES statement can be thought of as a set. And sets may be empty...

there is nothing mentioned about empty sets in the documentation for: Table Value Constructor

Answer 1

There is no simple syntactical solution, but you could work around the issue by making your row generator a little more complex.

You could rewrite the part that generates rows to replace the VALUES constructor with a set of

SELECT values
UNION ALL
SELECT values
UNION ALL
...

including an obligatory SELECT of this kind:

SELECT NULL, NULL, ... WHERE 1 <> 1

either at the beginning or at the end. That obligatory item will produce no rows and if it happens to be the only element, then the entire union will produce no rows and the INSERT statement will insert nothing.

If the VALUES constructor is preferable for some reason, you could modify your generator to include a (NULL, NULL, ...) item and wrap a query around the constructor:

INSERT INTO
  target_table (target_columns)
SELECT
  *
FROM
  (VALUES
    (...),
    ...
    (NULL, ...)  -- obligatory element
  ) AS v (column_names)
WHERE
  some_of_the_columns IS NOT NULL
;

where some_of_the_columns should be chosen among the columns for which a NULL can never be generated. Again, if the (NULL, ...) row happens to be the only generated row in VALUES, the resulting query will insert nothing.

Answer 2

The only usecase for this I can imagine is that you want to read forward through a large datastream and know at the end of a record whether there are values to insert or not. You can rewrite your 'insert values' to 'insert into select' and add a condition at the end that will prevent an insert.

insert into myTable(col1, col2)
select null, null
where 1 = 2

Answer 3

Set theory recognizes an empty set but an insert values does not

Couple possible options:
Insert nulls and have an insert trigger that voids the insert
Insert nulls and have a view that excludes them