Multirow inserts

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

insert

asked 07 Oct '12, 20:53

LGregianin
276●6●13●20
accept rate: 40%

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:(

(09 Oct '12, 03:23) Volker Barth

flat view

5 Answers:

active answers oldest answers newest answers popular answers

CREATE TABLE IF NOT EXISTS TEST(
        EMP char(3) NOT NULL,
        COD integer NOT NULL,
        DESCR char(60) NULL,
        PRIMARY KEY (EMP, COD) 
);

INSERT INTO TEST
VALUES ('001',1,'TEST 1'), 
       ('001',2,'TEST 2'), 
       ('001',3,'TEST 3');
       ('001',4,'TEST 4'), 
       ('001',5,'TEST 5'), 
       ('001',6,'TEST 6');
       ('001',7,'TEST 7'), 
       ('001',8,'TEST 8'), 
       ('001',9,'TEST 9');

Thanks a lot.

permanent link

answered 08 Oct '12, 14:41

LGregianin
276●6●13●20
accept rate: 40%

wikified 08 Oct '12, 15:16

flat view

Do you mean a VALUES clause on an INSERT statement that permits multiple rows? Something like this:

INSERT INTO T VALUES( (col1_value, col2_value), (col1_value, col2_value), ... )

This is supported in version 12 - I cannot recall whether or not this shipped in 12.0.0 or 12.0.0. See the documentation at dcx.sybase.com.

If you are thinking of another construction (ie. multi-row inserts thru JDBC or ODBC), these are also supported and have been for some time.

permanent link

answered 07 Oct '12, 21:42

Glenn Paulley
10.8k●5●76●106
accept rate: 43%

FWIW, this enhancement has been introduced with v12.0.0, cf. this page:

INSERT statement enhancements
The following enhancements have been made to the INSERT statement. See INSERT statement.

Support for more than one list of values
An INSERT statement can now contain more than one list of values, allowing several rows to be inserted at once. For example:
INSERT INTO T (c1,c2,c3)
VALUES (1,10,100), (2,20,200), (3,30,300);

Support for inserting rows with all default values
SQL Anywhere allows the VALUES clause to contain specified values for a subset of the columns in the table. All unspecified columns are given default values as specified for each column by means of DEFAULT, NULL and COMPUTE clauses of the CREATE TABLE statements. Previously, the database server required that you specify input values for at least one of the columns in the table.

(08 Oct '12, 03:23) Volker Barth

flat view

The multiple values option in 12.0.0 is great; but if you have an old version or want to do some SQL substitutions related to a fixed set of data in your Stored Procedure, try using OpenXML. I do the following when building up a set of error messages that I want to hardcode (for maintenance reasons) into the code and that have to be translated using tables in system:

create table #E (idSituationCUR unsigned integer, txMessage char(150), idSeqRead integer default 0);

-- using openxml to simplify the load and set a default value should the Join with CodelistTranslation
-- fail to find a corresponding entry
insert into #E (idSituationCUR, txMessage)
select LOAD.idSituationCUR, isnull(CLT.txOther,LOAD.txOther)
from OPENXML( string ('<a>',
  '<b c1="6201" c2="REF"                                    c3="',@kSituation_APPENDED,'"/>',
  '<b c1="6202" c2="CODE USED BY 2+ ITEMS, FIX ONLINE ONLY" c3="',@kSituation_MULTICDs,'"/>',
  '<b c1="6203" c2="UNKNOWN/CAN ADD"                        c3="',@kSituation_NOTFOUND,'"/>',
  '</a>'),
  'a/b' )
with ( idKey integer '@c1', txOther text '@c2', idSituationCUR unsigned integer '@c3') AS LOAD
left outer join BOS.CodelistLanguage CLT 
  on CLT.idCodelist = 238 and CLT.idLanguage = @idLanguage and CLT.idKey = LOAD.idKey;

permanent link

This answer is marked "community wiki".

answered 09 Oct '12, 02:56

Eric Hofer
46●1●1●6
accept rate: 0%

edited 09 Oct '12, 02:59

flat view

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

permanent link

answered 16 May '16, 19:59

Gregory Durniak
139●9●10●17
accept rate: 0%

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

(17 May '16, 02:56) Volker Barth

Replies hidden

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

greg

(17 May '16, 20:41) Gregory Durniak

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.

(18 May '16, 00:59) Volker Barth

flat view

-1

For older versions without the repeating values enhancement or where you might be doing a translation substitution, the following works..

  create table #E (idSituationCUR unsigned integer, txMessage char(150), idSeqRead integer default 0);  
-- using openxml to simplify the load and set a default value should the Join with 
  -- CodelistTranslation fail to find a corresponding entry
  insert into #E (idSituationCUR, txMessage)
  select LOAD.idSituationCUR, isnull(CLT.txOther,LOAD.txOther)
  from OPENXML( string ('',
      '',
      '',
      '',
      ''),
    'a/b' )
  with ( idKey integer '@c1', txOther text '@c2', idSituationCUR unsigned integer '@c3') AS LOAD
  left outer join BOS.CodelistLanguage CLT on CLT.idCodelist = 238 and CLT.idLanguage = @idLanguage and CLT.idKey = LOAD.idKey;

permanent link

This answer is marked "community wiki".

answered 09 Oct '12, 02:50

Eric Hofer
46●1●1●6
accept rate: 0%

edited 17 May '16, 04:47

Reimer Pods
4.5k●38●48●91

flat view

Your answer

toggle preview

community wiki:

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

learn more about Markdown

Question tags:

insert ×34

question asked: 07 Oct '12, 20:53

question was seen: 11,659 times

last updated: 18 May '16, 01:06

SAP SQL Anywhere

Multirow inserts

Follow this question

Related questions