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 answered 17 Sep '21, 03:12 Frank Vestjens 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.
(17 Sep '21, 06:20)
Volker Barth
|
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. answered 17 Sep '21, 06:17 Volker Barth |
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. answered 17 Sep '21, 07:48 beater That's why I asked what you want to achieve. I think a stored procedure could do the trick
(17 Sep '21, 07:57)
Frank Vestjens
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...
(17 Sep '21, 09:06)
Volker Barth
|