ROWTYPE insert

[former_SQLA_member1694938]

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

[fvestjens]

I don't know what you're trying to achieve, but this is another solution of what you're trying:

DECLARE LOCAL TEMPORARY TABLE @tblMT like MyTable;
INSERT INTO @tblMT SELECT * FROM MyTable WHERE ID = 0;
INSERT INTO MyTable SELECT * FROM @tblMT

Another solution could be a stored procedure and passing MyTable and @mtRow as parameters and create an execute immediate statement to insert the data from @mtRow in MyTable

[VolkerBarth]

I do agree, "SELECT INTO ..." / "INSERT SELECT FROM" are the easiest means to do schema-agnostic copies of complete rows between tables, and with AUTO NAME you can even adjust some column names.

[former_SQLA_member1694938]

ok thank you all. That type of sytax I posted is possible in Oracle and Postgresql so I thought it might have been possible in SQL Anywhere. The Columns will change and I do not want to have to keep changing the procedure every time.

[fvestjens]

That's why I asked what you want to achieve. I think a stored procedure could do the trick

[VolkerBarth]

As stated above, I think SQL Anywhere favours SELECT...INTO / INSERT ... SELECT to move/copy rows between tables in a schema-agnostic way, optionally via local temporary tables. And with CREATE TABLE...LIKE, TABLE REFs data types and indirect identifiers it should be easy to create procedures to deal with such moves independent of the particular tables in question.

For further suggestions, please provide a better sample or task - copying one single row within the same table would apparently only need one single INSERT...SELECT statement in every common SQL database, without need for any variables at all...

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