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

asked 16 Sep, 14:32

beater's gravatar image

beater
545
accept rate: 0%


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

permanent link

answered 17 Sep, 03:12

Frank's gravatar image

Frank
741222745
accept rate: 21%

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

permanent link

answered 17 Sep, 06:17

Volker%20Barth's gravatar image

Volker Barth
37.7k347514779
accept rate: 34%

edited 17 Sep, 06:18

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.

permanent link

answered 17 Sep, 07:48

beater's gravatar image

beater
545
accept rate: 0%

edited 17 Sep, 08:03

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

(17 Sep, 07:57) Frank

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, 09:06) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×3

question asked: 16 Sep, 14:32

question was seen: 125 times

last updated: 17 Sep, 09:07