Multirow inserts

[Former Member]

Sybase has the ability to make an insertion multi rows? If yes, what version is available? If not, has plans to develop?

[VolkerBarth]

Such a pity that "open database documentation" like this page doesn't list the great features of SQL Anywhere... sometimes the "invisible database" is way too invisible to get its deserved audience:(

[Former Member]

For older versions ( e.g. 10 ), this seems to work:

INSERT INTO T ( "col1", "col2" ) ( ( SELECT '12', '34' FROM T2 ) UNION ( SELECT '56', '78' FROM T2 ) )

greg

[VolkerBarth]

Yes, this is a simple INSERT...SELECT and will work even with very very old SQL Anywhere versions... - note, you can omit the brackets around the SELECT statement and omit the (dummy) table name, such as

CREATE TABLE T (col1 VARCHAR(10), col2 VARCHAR(10));
INSERT INTO T (col1, col2)
   SELECT '12', '34' UNION SELECT '56', '78';

(I'm note sure whether the OP was asking for that...)

[Former Member]

With this method, Is there a limit on the number of rows inserted ?

greg

[VolkerBarth]

I don't think there is an explicit limit on the number of rows aka the number of times you can use the UNION operator to combine query blocks... (or it would be same as the (huge) maximum number of rows per table, so it would not matter how you try to insert them). - I would guess that the overall length of the resulting statement might have its limits, possibly dependent of the API used, but then again that would hold for the INSERT ... VALUES (...), (...) statement, too. - Usually the particular hardware (memory, CPU, disk drives) will have a more limitating effect.

---

IMHO, when trying to insert big amounts of data that is not already contained in another table (possibly of another database) and thus prevents the use of a simple INSERT SELECT statement, I would not use INSERT with multple values but would prefer LOAD TABLE or SELECT ... FROM OPENSTRING()/OPENXML() or the like.