Unanswered: insert data from table1 to table2 & capture tablename
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,
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
How about this?
INSERT INTO table1 SELECT dms_dt , 'TABLE2' , ... , COUNT(*) FROM table2 GROUP BY dms_dt;
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.
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.