PeopleSoft Community Network
Create insert statement for tables for data migration where
ZZ84 is a new prefix for temporary tables.
SELECT DECODE(COLUMN_ID ,1,'INSERT INTO '
||REPLACE(table_name,'PS_','PS_ZZ84_')
||' SELECT ',' ')
||COLUMN_NAME
||DECODE(COLUMN_ID,MAX_COLUMN,'',',')
||DECODE(COLUMN_ID,MAX_COLUMN ,' FROM SYSADM.'||TABLE_NAME||'@DBLINKTOP8_4'||'/' ,'') AS TEXT
FROM all_tab_columns T,(SELECT TABLE_NAME AS TABLE_NAME1,
MAX(COLUMN_ID) AS MAX_COLUMN
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME LIKE 'PS_EX_%'
GROUP BY TABLE_NAME) T1
WHERE REPLACE(table_name,'PS_','PS_ZZ84_') IN ('PS_ZZ84_EX_PROJ_RES_STG', 'PS_ZZ84_EX_TIME_DTL', 'PS_ZZ84_EX_TIME_DTL_ADJ', 'PS_ZZ84_EX_TIME_DTL_DLY', 'PS_ZZ84_EX_TIME_HDR', 'PS_ZZ84_EX_TIME_POL', 'PS_ZZ84_EX_TIME_POL_ADJ', 'PS_ZZ84_EX_TIME_POL_DLY')
AND T1.TABLE_NAME1=T.TABLE_NAME;
Tags:
© 2024 Created by Maisam Agha.
Powered by