in Table-A I have 10 records, and Table-B is empty.
now I wanted to write a query like....
Wanted to select all the data from table-A, and insert into table-B. After inserting the records into table-B, I wanted to verify whether all the records have been copied or not? If counts are okay then I wanted to update the time stamp.
Right now I have written a query like below. now I wanted to add that above condition to this query.
Joined: 22 Nov 2005 Posts: 700 Location: Troy, Michigan USA
CrrIndia,
First, you need to have faith in DB2 to do what you told it to do, unless it failed.
If I wanted to copy from 1 table to another and update with CURRENT_TIMESTAMP, I would:
Code:
INSERT INTO P2CE3P01.CE_ENVR
(CLNT_ID,
ENVR_DATA_TX,
ENVR_NM,
ENVR_TS,
PRVS_NMBR_ID)
SELECT CLNT_ID,
ENVR_DATA_TX,
ENVR_NM,
CURRENT_TIMESTAMP,
PRVS_NMBR_ID
FROM Z0CE3Z01.CE_ENVR
;
However, If you insist in checking the counts:
Code:
INSERT INTO P2CE3P01.CE_ENVR
(CLNT_ID,
ENVR_DATA_TX,
ENVR_NM,
ENVR_TS,
PRVS_NMBR_ID)
SELECT CLNT_ID,
ENVR_DATA_TX,
ENVR_NM,
ENVR_TS,
PRVS_NMBR_ID
FROM Z0CE3Z01.CE_ENVR
;
UPDATE P2CE3P01.CE_ENVR
SET ENVR_TS = CURRENT_TIMESTAMP
WHERE EXISTS
(SELECT A.COUNT_A
FROM (SELECT COUNT(*) AS COUNT_A
FROM Z0CE3Z01.CE_ENVR
) A,
(SELECT COUNT(*) AS COUNT_B
FROM P2CE3P01.CE_ENVR
) B
WHERE A.COUNT_A = B.COUNT_B
)
;
.jpg)
