Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups 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 unload data from table with lob columns farhad_evan DB2 0 Sat Apr 22, 2017 1:32 pm
No new posts Data replication from multiple Db2 ta... kishpra DB2 9 Mon Mar 27, 2017 9:58 pm
No new posts Mass comment and edit elixir1986 IBM Tools 3 Thu Mar 02, 2017 10:09 pm
No new posts how to send just 10 rows in a CICS sc... Megha Gupta CICS 5 Thu Feb 23, 2017 6:57 pm
No new posts Join of more than one table dibyendumandal11111 DB2 6 Mon Feb 13, 2017 11:36 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us