ROWTYPE insert

[#1]

Is there a way to insert a ROWTYPE?
I thought something like this would work but it does not:
DECLARE @mtRow MyTable%ROWTYPE;
SELECT * INTO @mtRow FROM MyTable where ID=0;
INSERT INTO MyTable values (@mtRow);

[VolkerBarth]

I don't think your interesting approach is possible with SQL Anywhere 17, particularly as there seems no way to INSERT FROM a ROW type without specifying its individual fields. (*)

Here's a sample based on the demo database's "Contacts" table.

BEGIN
   DECLARE rt Contacts%ROWTYPE;
   DROP TABLE IF EXISTS MyContacts;
   CREATE TABLE MyContacts LIKE Contacts INCLUDING ALL;

   -- SELECT INTO a ROW type is supported
   SELECT * INTO VARIABLE rt FROM Contacts where ID = 1;

   -- INSERT requires listing each element
   -- (would also work via SELECT rt.ID, rt.Surname...)
   INSERT INTO MyContacts
   VALUES (rt.ID, rt.Surname, rt.GivenName, rt.Title,
      rt.Street, rt.City, rt."State", rt.Country, rt.PostalCode,
      rt.Phone, rt.Fax, rt.CustomerID);

   SELECT * FROM MyContacts;

   -- Desirable - but not (yet?) supported with 17.0.10.6315
   -- SELECT rt.*; -- invalid syntax
   -- SELECT * FROM rt; -- invalid syntax, a ROW type is no valid table-expression
END;

---

(*) Note, this is different from an ARRAY type, as UNNEST() can be used to "flatten" array contents to a valid FROM clause expression.