Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Mass insert - insert into the table which is queried

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Daphne

New User


Joined: 28 Aug 2006
Posts: 27
Location: NY, US

PostPosted: Wed Dec 20, 2006 11:35 pm    Post subject: Mass insert - insert into the table which is queried
Reply with quote

Hi all,

I have a table <table1> with columns <prev year>, <col2>, <col3> , <2>.

I have thousands of rows in this table.

Every start of the year I have to change the previous year into current year (no update) and change <2> to <3> and insert the row in the same table.

Once this is done we will have two rows
<prev year>, <col2>, <col3>, <2>
<current year>, <col2>, <col3>, <3>

I am intending to use mass insert using select, but not sure how to put the constants in between (year and 3).

Appreciate any help.

Thanks,
Daphne
Back to top
View user's profile Send private message

DavidatK

Active Member


Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA

PostPosted: Thu Dec 21, 2006 1:44 am    Post subject: Re: Mass insert - insert into the table which is queried
Reply with quote

Daphne, Is this what you are looking to happen?

Code:


DECLARE GLOBAL TEMPORARY TABLE TEMP_TBL   
  (                                       
       C_YEAR                   DEC(4),   
       COL2                     DEC(3),   
       COL3                    CHAR(1),   
       INCR                     DEC(3)     
   )                                       
;                                         

SELECT *                                               
  FROM SESSION.TEMP_TBL                                 
;                                                       
INSERT                                                 
  INTO SESSION.TEMP_TBL                                 
       (C_YEAR,                                         
        COL2,                                           
        COL3,                                           
        INCR)                                           
SELECT 2007,                                           
       COL2,                                           
       COL3,                                           
       3                                               
  FROM SESSION.TEMP_TBL                                 
 WHERE C_YEAR = 2006                                   
   AND INCR = 2                                         
;                                                       
SELECT *                                               
  FROM SESSION.TEMP_TBL                                 
;                                                       



First SELECT*
Code:

   +----------------------------------+
   | C_YEAR  |  COL2  | COL3 |  INCR  |
   +----------------------------------+
 1_|  2005   |   15   | A    |    1   |
 2_|  2005   |   17   | B    |    2   |
 3_|  2006   |   15   | A    |    2   |
 4_|  2006   |   17   | B    |    2   |
   +----------------------------------+
                                       


Second SELECT*
Code:

    +----------------------------------+
    | C_YEAR  |  COL2  | COL3 |  INCR  |
    +----------------------------------+
  1_|  2005   |   15   | A    |    1   |
  2_|  2005   |   17   | B    |    2   |
  3_|  2006   |   15   | A    |    2   |
  4_|  2006   |   17   | B    |    2   |
  5_|  2007   |   15   | A    |    3   |
  6_|  2007   |   17   | B    |    3   |
    +----------------------------------+
                                       
Back to top
View user's profile Send private message
Daphne

New User


Joined: 28 Aug 2006
Posts: 27
Location: NY, US

PostPosted: Thu Dec 21, 2006 2:36 am    Post subject: Re: Mass insert - insert into the table which is queried
Reply with quote

Hi Dave,

Thanks for your explanative reply. Yes this is what I was looking for.

I am using embedded SQL. I was trying different ways and at last got into the same solution(well...almost :-) ).


INSERT INTO <tablename>
SELECT '2007'
, A.col2
, A.col3
, '3'
FROM <tablename> A
WHERE A.YEAR = '2006'
AND A.col4 = '2'


Thanks again,
Daphne
Back to top
View user's profile Send private message
DavidatK

Active Member


Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA

PostPosted: Thu Dec 21, 2006 2:45 am    Post subject: Re: Mass insert - insert into the table which is queried
Reply with quote

Well, I'm glad we came up with similar solutions. They validate each other icon_smile.gif

Dave
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Insert a Row_number into table useit DB2 2 Tue Sep 19, 2017 1:07 pm
No new posts how to see when the last read access ... Mike 1304 DB2 1 Tue Sep 12, 2017 7:52 pm
No new posts PC (UTF-8) -> z/OS (EBCDIC) -> ... prino All Other Mainframe Topics 4 Fri Sep 01, 2017 1:47 am
No new posts Adding a userid to SMTP Security table Yolanda Harvey JCL & VSAM 1 Sun Aug 13, 2017 6:16 pm
No new posts Loading data to table gives wrong for... Raghu navaikulam DB2 19 Thu Jul 13, 2017 2:11 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us