Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2010
    Posts
    3

    Exclamation Unanswered: insert data from table1 to table2 & capture tablename

    hi guys..

    i have a problem here..hope someone could help me
    i have to create a table consist of 4 column.
    CREATE TABLE "TABLE1"
    ("DMS_DT" DATE NOT NULL,
    "TBLNAME" VARCHAR(10) NOT NULL,
    "REC_LD_TS" TIMESTAMP NOT NULL,
    "REC_COUNT" INTEGER NOT NULL,
    )
    IN EDWOWN

    my question is how can i coded in DB2 for all req. below;
    *for DMS_DT and REC_LD_TS data must be taken from TABLE2.
    *for REC_COUNT, data must be computed base on TABLE2 total count group by the DMS_DT
    *for TBLNAME it must return 'TABLE2'. logically i could just hard coded the table name as parameter.but how could i achieve that in DB2

    hope some one could help me

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about this?
    INSERT INTO table1 SELECT dms_dt , 'TABLE2' , ... , COUNT(*) FROM table2 GROUP BY dms_dt;

    Some issues:
    1) Which REC_LD_TS should take for a DMS_DT?
    2) I like to remove double quotations for each object names.
    If that were removed, the object names would be recorded in upper case in DB2 system catalog tables/views.

  3. #3
    Join Date
    Oct 2010
    Posts
    3
    INSERT INTO edwown.TABLE1
    (
    DMS_DT, REC_LD_TS, REC_COUNT,*TBLNAME
    )
    SELECT
    DMS_DT, REC_LD_TS, count (*)
    FROM edwown.TABLE2
    group by DMS_DT

    it might be somthing like this.
    *how am i going to retrieve the TBLNAME info? (most tricky part)

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Why don't you want to copy the table name from FROM clause?

    If you want to execute the insert statement in a procedure, you can use dynamic SQL(EXECUTE IMMEDIATE statement).
    And you can use same parameter(or SQL variable) for a literal in SELECT list and a table name in FROM clause to construct a SQL text to be executed.

  5. #5
    Join Date
    Oct 2010
    Posts
    3

    Smile

    finally manage to get it..so simple


    INSERT INTO edwown.table1
    (
    DMS_DT, REC_LD_TS, REC_COUNT, TBLNAME
    )
    SELECT
    DMS_DT, REC_LD_TS, count (*) as REC_COUNT, (VALUES 'table2') as TBLNAME
    FROM edwown.table2
    GROUP BY DMS_DT, REC_LD_TS

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ... (VALUES 'table2') ...
    UPPER('table2') will return same value(i.e. 'TABLE2').

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Did you required GROUP BY DMS_DT(not GROUP BY DMS_DT, REC_LD_TS)?

    *for REC_COUNT, data must be computed base on TABLE2 total count group by the DMS_DT

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •