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;

Views: 45

Reply to This

Replies to This Discussion

it's a pleasure to meet you. I am USA Army personnel, i have an important thing to discus with you.
Please write me on my email (captkristen899@gmail.com)

RSS

PeopleSoft Jobs in US

Videos

  • Add Videos
  • View All

© 2020   Created by Maisam Agha.   Powered by

Badges  |  Report an Issue  |  Terms of Service