inserting more than one row thru select statement



IBM's flagship relational database management system

inserting more than one row thru select statement

Postby sri_mf » Thu Feb 25, 2016 9:56 pm

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;
sri_mf
 
Posts: 8
Joined: Thu Feb 04, 2016 1:54 pm
Has thanked: 0 time
Been thanked: 0 time

Re: inserting more than one row thru select statement

Postby NicC » Thu Feb 25, 2016 10:47 pm

And your error is? And the manual says with regard to that error code?
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 3025
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisies (almost)
Has thanked: 4 times
Been thanked: 136 times

Re: inserting more than one row thru select statement

Postby sri_mf » Thu Feb 25, 2016 10:58 pm

error

SQLCODE = -533, ERROR: INVALID MULTIPLE-ROW INSERT
sri_mf
 
Posts: 8
Joined: Thu Feb 04, 2016 1:54 pm
Has thanked: 0 time
Been thanked: 0 time

Re: inserting more than one row thru select statement

Postby Akatsukami » Fri Feb 26, 2016 1:03 am

Reading the fine manual makes obvious what you have done wrong.
"You have sat too long for any good you have been doing lately ... Depart, I say; and let us have done with you. In the name of God, go!" -- what I say to a junior programmer at least once a day
User avatar
Akatsukami
Global moderator
 
Posts: 1058
Joined: Sat Oct 16, 2010 2:31 am
Location: Bloomington, IL
Has thanked: 6 times
Been thanked: 51 times

Re: inserting more than one row thru select statement

Postby sri_mf » Fri Feb 26, 2016 7:12 am

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;
sri_mf
 
Posts: 8
Joined: Thu Feb 04, 2016 1:54 pm
Has thanked: 0 time
Been thanked: 0 time


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post