Page 1 of 1

inserting more than one row thru select statement

PostPosted: Thu Feb 25, 2016 9:56 pm
by sri_mf
insert thru select is giivng error for multiple rows insert

---- copying data from db2 shipped sample databse

beow are table descriptions

CREATE TABLE  DEPT
      (DEPTNO    CHAR(3)           NOT NULL,
       DEPTNAME  VARCHAR(36)       NOT NULL,
       MGRNO     CHAR(6)                   ,
       ADMRDEPT  CHAR(3)           NOT NULL,
       LOCATION  CHAR(16)                  ,
       PRIMARY KEY (DEPTNO)                )
  IN mydatabase

ALTER TABLE  DEPT
      FOREIGN KEY RDD (ADMRDEPT) REFERENCES DBMATE01.DEPT
              ON DELETE CASCADE;


following are the indexes
DEPTNO Primary, ascending
MGRNO Ascending
ADMRDEPT Ascending

my insert query
insert into dept
select * from DSN8910.dept;

Re: inserting more than one row thru select statement

PostPosted: Thu Feb 25, 2016 10:47 pm
by NicC
And your error is? And the manual says with regard to that error code?

Re: inserting more than one row thru select statement

PostPosted: Thu Feb 25, 2016 10:58 pm
by sri_mf
error

SQLCODE = -533, ERROR: INVALID MULTIPLE-ROW INSERT

Re: inserting more than one row thru select statement

PostPosted: Fri Feb 26, 2016 1:03 am
by Akatsukami
Reading the fine manual makes obvious what you have done wrong.

Re: inserting more than one row thru select statement

PostPosted: Fri Feb 26, 2016 7:12 am
by sri_mf
yes the errror says it doesn't allow multple row insert
and sujests to insert by one row at time,,,,,


but i like to know whats the reason for failure of multiple insert ,,, is it due to index or primary key ?
as i executed with different table and it was successful

sucessfu  execution of below query
     CREATE TABLE  ACT
      (ACTNO    SMALLINT       NOT NULL,
       ACTKWD   CHAR(6)        NOT NULL,
       ACTDESC  VARCHAR(20)    NOT NULL,
       PRIMARY KEY (ACTNO)             )
  IN mydatabase
 insert into act
  select * from DSN8910.act;