API tools faq
paste
Advertisement
yogirk

Export tables to Oracle- Linked Server

yogirk
Feb 1st, 2012
300
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 8.56 KB | None | 0 0
raw download clone embed print report
  1. --B_RPT_ASP_OPSMART_CASES_TBL             PSRPT_CASES_PSFT
  2. --B_RPT_ASP_OPSMART_CONTACT_TBL           PSRPT_USER_PSFT
  3. --B_RPT_ASP_OPSMART_CASE_COUNT_TBL        PSRPT_CASES_BY_MONTH_PSFT
  4. --B_RPT_ASP_OPSMART_IPRD_TBL              PSRPT_PSFT
  5. --B_RPT_B_TEAM_ROLE                       PSRPT_TEAM_ROLES_PSFT
  6.  
  7.  
  8.  
  9. -- DROP TABLES ORACLE
  10.  
  11. SELECT getdate() 'Drop Oracle Tables'
  12.  
  13. EXECUTE ( 'DROP TABLE ZMMSUSER.PSRPT_CASES_PSFT' ) AT ZMMS_ORA;
  14. EXECUTE ( 'DROP TABLE ZMMSUSER.PSRPT_USER_PSFT') AT ZMMS_ORA;
  15. EXECUTE ( 'DROP TABLE ZMMSUSER.PSRPT_CASES_BY_MONTH_PSFT') AT ZMMS_ORA;
  16. EXECUTE ( 'DROP TABLE ZMMSUSER.PSRPT_PSFT') AT ZMMS_ORA;
  17. EXECUTE ( 'DROP TABLE ZMMSUSER.PSRPT_TEAM_ROLES_PSFT') AT ZMMS_ORA;
  18.  
  19.  
  20. -- CREATE TABLES ON ORACLE
  21. SELECT getdate() 'Create Tables on Oracle'
  22.  
  23. EXECUTE (
  24.         'CREATE TABLE "ZMMSUSER"."PSRPT_CASES_PSFT" (
  25.         "COMPANYID" NVARCHAR2(15) NOT NULL,
  26.         "CASE_ID" NUMBER(15, 0) NOT NULL,
  27.         "ROW_ADDED_DTTM" DATE,
  28.         "CLOSED_DATE" DATE,
  29.         "PROVIDER_GRP_ID" NVARCHAR2(100) NULL
  30. )'
  31.         ) AT ZMMS_ORA;
  32. EXECUTE (
  33.         'CREATE TABLE "ZMMSUSER"."PSRPT_CASES_BY_MONTH_PSFT" (
  34.         "COMPANYID" NVARCHAR2(15) NOT NULL,
  35.         "PROVIDER_GRP_ID" NVARCHAR2(100)  NULL,
  36.         "TOTAL_CASES" INTEGER,
  37.         "MONTH_CREATED" INTEGER,
  38.         "YEAR_CREATED" INTEGER
  39. )'
  40.         ) AT ZMMS_ORA;
  41.  
  42. EXECUTE (
  43.         'CREATE TABLE "ZMMSUSER"."PSRPT_USER_PSFT" (
  44.         "COMPANYID" NVARCHAR2(15) NOT NULL,
  45.         "OPRID" NVARCHAR2(30) NULL,
  46.         "FIRST_NAME" NVARCHAR2(30) NULL,
  47.         "LAST_NAME" NVARCHAR2(30) NULL,
  48.         "MIDDLE_NAME" NVARCHAR2(30) NULL,
  49.         "TITLE" NVARCHAR2(35) NULL,
  50.         "PERSON_ID" NVARCHAR2(15) NULL,
  51.         "CONTACT_EMAIL" NVARCHAR2(70) NULL,
  52.         "LS_SYS_ADMIN" VARCHAR2(1) NULL,
  53.         "LS_PRIMARY_CONTACT" VARCHAR2(1) NULL,
  54.         "WCT_SYS_ADMIN" VARCHAR2(1) NULL,
  55.         "WCT_PRIMARY_CONTACT" VARCHAR2(1) NULL,
  56.         "PRIMARY_PHONE" NVARCHAR2(35)
  57. )'
  58.         ) AT ZMMS_ORA;
  59. EXECUTE (
  60.         'CREATE TABLE "ZMMSUSER"."PSRPT_PSFT" (
  61.         "BO_NAME" NVARCHAR2(50) NOT NULL,
  62.         "COMPANYID" NVARCHAR2(15) NOT NULL,
  63.         "CUST_STATUS" NVARCHAR2(10) NULL,
  64.         "CUSTOMER_TYPE" NVARCHAR2(10) NULL,
  65.         "PRODUCT_ID" NVARCHAR2(18) NULL,
  66.         "DESCR" NVARCHAR2(30) NOT NULL,
  67.         "B_VERSION" NVARCHAR2(15) NULL,
  68.         "B_VERSION_BUILD" NVARCHAR2(15) NULL,
  69.         "ATTRIBUTE_VALUE" NVARCHAR2(254) NULL,
  70.         "INST_PROD_ID" NVARCHAR2(20) NOT NULL,
  71.         "ORDER_DATE" DATE,
  72.         "INSTALLED_DATE" DATE,
  73.         "PLATFORM" NVARCHAR2(3) NULL,
  74.         "OS" NVARCHAR2(4) NULL,
  75.         "OS_VERSION" NVARCHAR2(15) NULL,
  76.         "STATE" NVARCHAR2(6) NULL,
  77.         "INDUSTRY_ID" NVARCHAR2(30) NULL,
  78.         "ICM" NVARCHAR2(17) NULL,
  79.         "LEARN_CLIENT_SEGMENT" NVARCHAR2(30) NULL,
  80.         "INST_PROD_STATUS" NVARCHAR2(4) NULL
  81. )'
  82.         ) AT ZMMS_ORA;
  83.    
  84. EXECUTE (
  85.        
  86.         'CREATE TABLE "ZMMSUSER"."PSRPT_TEAM_ROLES_PSFT" (
  87.         "COMPANYID" NVARCHAR2(15) NOT NULL,
  88.         "BO_ID" NUMBER(31, 0) NOT NULL,
  89.     `   "AS CM" NVARCHAR2(61),
  90.         "AS RSM" NVARCHAR2(61),
  91.         "AS SE" NVARCHAR2(61),
  92.         "TS CM" NVARCHAR2(61),
  93.         "TS RAE" NVARCHAR2(61),
  94.         "TS RSM" NVARCHAR2(61),
  95.         "TS SE" NVARCHAR2(61),
  96.         "BBONE AM" NVARCHAR2(61),
  97.         "ASM" NVARCHAR2(61),
  98.         "WCT ASM" NVARCHAR2(61),
  99.         "LS TSM" NVARCHAR2(61),
  100.         "WCT TSM" NVARCHAR2(61),
  101.         "TS TSM" NVARCHAR2(61),
  102.         "AS PM" NVARCHAR2(61),
  103.         "AS DEV" NVARCHAR2(61),
  104.         "TS PM" NVARCHAR2(61),
  105.         "TS DEV" NVARCHAR2(61),
  106.         "TS TECH" NVARCHAR2(61),
  107.         "BB EXEC" NVARCHAR2(61),
  108.         "CHM" NVARCHAR2(61),
  109.         "ANGEL Client Manager (CM)" NVARCHAR2(61),
  110.         "ANGEL Engagement Team" NVARCHAR2(61),
  111.         "BbOne Client Manager (CM)" NVARCHAR2(61),
  112.         "Blackboard Executive" NVARCHAR2(61),
  113.         "CE/Vista Hosting Mgr (ASM)" NVARCHAR2(61),
  114.         "CE/Vista Tech Support (TSM)" NVARCHAR2(61),
  115.         "Complex Hosting Manager (CHM)" NVARCHAR2(61),
  116.         "Learn Client Manager (CM)" NVARCHAR2(61),
  117.         "Learn Hosting Mgr (ASM)" NVARCHAR2(61),
  118.         "Learn Reg Sales Mgr (RSM)" NVARCHAR2(61),
  119.         "Learn Sales Engineer (SE)" NVARCHAR2(61),
  120.         "Learn Services Developer" NVARCHAR2(61),
  121.         "Learn Services ICM Mgr" NVARCHAR2(61),
  122.         "Learn Services Project Manager" NVARCHAR2(61),
  123.         "Learn Tech Support (TSM)" NVARCHAR2(61),
  124.         "Transact Client Manager (CM)" NVARCHAR2(61),
  125.         "Transact Project Manager" NVARCHAR2(61),
  126.         "Transact Reg Acct Exec (RAE)" NVARCHAR2(61),
  127.         "Transact Reg Sales Mgr (RSM)" NVARCHAR2(61),
  128.         "Transact Sales Engineer (SE)" NVARCHAR2(61),
  129.         "Transact Services Technician" NVARCHAR2(61),
  130.         "Transact Tech Support (TSM)" NVARCHAR2(61),
  131.         "Xythos Tech Support (TSM)" NVARCHAR2(61)
  132. )'
  133.         ) AT ZMMS_ORA;
  134.  
  135. -- Export Tables
  136.  
  137. SELECT getdate() 'Start PSRPT_PSFT export'
  138. INSERT INTO OPENQUERY (
  139.     ZMMS_ORA
  140.     ,'SELECT COMPANYID, CASE_ID, ROW_ADDED_DTTM, CLOSED_DATE, PROVIDER_GRP_ID FROM ZMMSUSER.PSRPT_CASES_PSFT'
  141.     )
  142. SELECT COMPANYID
  143.     ,CASE_ID
  144.     ,ROW_ADDED_DTTM
  145.     ,CLOSED_DATE
  146.     ,PROVIDER_GRP_ID
  147. FROM CRMRPT..B_RPT_ASP_OPSMART_CASES_TBL
  148.  
  149. SELECT getdate() 'Start PSRPT_USER_PSFT export'
  150.  
  151. INSERT INTO OPENQUERY (
  152.     ZMMS_ORA
  153.     ,'SELECT COMPANYID,
  154.         OPRID,
  155.         FIRST_NAME,
  156.         LAST_NAME,
  157.         MIDDLE_NAME,
  158.         TITLE,
  159.         PERSON_ID,
  160.         CONTACT_EMAIL,
  161.         LS_SYS_ADMIN,
  162.         LS_PRIMARY_CONTACT,
  163.         WCT_SYS_ADMIN,
  164.         WCT_PRIMARY_CONTACT,
  165.         PRIMARY_PHONE FROM ZMMSUSER.PSRPT_USER_PSFT'
  166.     )
  167. SELECT COMPANYID
  168.     ,OPRID
  169.     ,FIRST_NAME
  170.     ,LAST_NAME
  171.     ,MIDDLE_NAME
  172.     ,TITLE
  173.     ,PERSON_ID
  174.     ,CONTACT_EMAIL
  175.     ,LS_SYS_ADMIN
  176.     ,LS_PRIMARY_CONTACT
  177.     ,WCT_SYS_ADMIN
  178.     ,WCT_PRIMARY_CONTACT
  179.     ,PRIMARY_PHONE
  180. FROM CRMRPT..B_RPT_ASP_OPSMART_CONTACT_TBL;
  181.  
  182. --Start PSRPT_CASES_BY_MONTH_PSFT export
  183. SELECT getdate() 'Start PSRPT_CASES_BY_MONTH_PSFT export'
  184.  
  185. INSERT INTO OPENQUERY (
  186.     ZMMS_ORA
  187.     ,' SELECT COMPANYID,
  188.         PROVIDER_GRP_ID,
  189.         TOTAL_CASES,
  190.         MONTH_CREATED,
  191.         YEAR_CREATED
  192.         FROM ZMMSUSER.PSRPT_CASES_BY_MONTH_PSFT'
  193.     )
  194. SELECT COMPANYID
  195.     ,PROVIDER_GRP_ID
  196.     ,TOTAL_CASES
  197.     ,MONTH_CREATED
  198.     ,YEAR_CREATED
  199. FROM CRMRPT..B_RPT_ASP_OPSMART_CASE_COUNT_TBL;
  200.  
  201. --Start PSRPT_PSFT Export
  202. SELECT getdate() 'Start PSRPT_PSFT export'
  203.  
  204. INSERT INTO OPENQUERY (
  205.     ZMMS_ORA
  206.     ,'SELECT
  207.         BO_NAME,
  208.         COMPANYID,
  209.         CUST_STATUS,
  210.         CUSTOMER_TYPE,
  211.         PRODUCT_ID,
  212.         DESCR,
  213.         B_VERSION,
  214.         B_VERSION_BUILD,
  215.         ATTRIBUTE_VALUE,
  216.         INST_PROD_ID,
  217.         ORDER_DATE,
  218.         INSTALLED_DATE,
  219.         PLATFORM,
  220.         OS,
  221.         OS_VERSION,
  222.         STATE,
  223.         INDUSTRY_ID,
  224.         ICM,
  225.         LEARN_CLIENT_SEGMENT,
  226.         INST_PROD_STATUS FROM ZMMSUSER.PSRPT_PSFT'
  227.     )
  228. SELECT BO_NAME
  229.     ,COMPANYID
  230.     ,CUST_STATUS
  231.     ,CUSTOMER_TYPE
  232.     ,PRODUCT_ID
  233.     ,DESCR
  234.     ,B_VERSION
  235.     ,B_VERSION_BUILD
  236.     ,ATTRIBUTE_VALUE
  237.     ,INST_PROD_ID
  238.     ,ORDER_DATE
  239.     ,INSTALLED_DATE
  240.     ,PLATFORM
  241.     ,OS
  242.     ,OS_VERSION
  243.     ,STATE
  244.     ,INDUSTRY_ID
  245.     ,ICM
  246.     ,LEARN_CLIENT_SEGMENT
  247.     ,INST_PROD_STATUS
  248. FROM CRMRPT..B_RPT_ASP_OPSMART_IPRD_TBL;
  249.  
  250. --Start PSRPT_TEAM_ROLES_PSFT Export
  251. SELECT getdate() 'Start PSRPT_TEAM_ROLES_PSFT export'
  252.  
  253. INSERT INTO OPENQUERY (
  254.     ZMMS_ORA
  255.     'SELECT
  256.         "COMPANYID",
  257.         "BO_ID",
  258.         "AS CM",
  259.         "AS RSM",
  260.         "AS SE",
  261.         "TS CM",
  262.         "TS RAE",
  263.         "TS RSM",
  264.         "TS SE",
  265.         "BBONE AM",
  266.         "ASM",
  267.         "WCT ASM",
  268.         "LS TSM",
  269.         "WCT TSM",
  270.         "TS TSM",
  271.         "AS PM",
  272.         "AS DEV",
  273.         "TS PM",
  274.         "TS DEV",
  275.         "TS TECH",
  276.         "BB EXEC",
  277.         "CHM",
  278.         "ANGEL Client Manager (CM)",
  279.         "ANGEL Engagement Team",
  280.         "BbOne Client Manager (CM)",
  281.         "Blackboard Executive",
  282.         "CE/Vista Hosting Mgr (ASM)",
  283.         "CE/Vista Tech Support (TSM)",
  284.         "Complex Hosting Manager (CHM)",
  285.         "Learn Client Manager (CM)",
  286.         "Learn Hosting Mgr (ASM)",
  287.         "Learn Reg Sales Mgr (RSM)",
  288.         "Learn Sales Engineer (SE)",
  289.         "Learn Services Developer",
  290.         "Learn Services ICM Mgr",
  291.         "Learn Services Project Manager",
  292.         "Learn Tech Support (TSM)",
  293.         "Transact Client Manager (CM)",
  294.         "Transact Project Manager",
  295.         "Transact Reg Acct Exec (RAE)",
  296.         "Transact Reg Sales Mgr (RSM)",
  297.         "Transact Sales Engineer (SE)",
  298.         "Transact Services Technician",
  299.         "Transact Tech Support (TSM)",
  300.         "Xythos Tech Support (TSM)"  FROM ZMMSUSER.PSRPT_TEAM_ROLES_PSFT'
  301.     )
  302. SELECT "COMPANYID"
  303.     ,"BO_ID"
  304.     ,"AS CM"
  305.     ,"AS RSM"
  306.     ,"AS SE"
  307.     ,"TS CM"
  308.     ,"TS RAE"
  309.     ,"TS RSM"
  310.     ,"TS SE"
  311.     ,"BBONE AM"
  312.     ,"ASM"
  313.     ,"WCT ASM"
  314.     ,"LS TSM"
  315.     ,"WCT TSM"
  316.     ,"TS TSM"
  317.     ,"AS PM"
  318.     ,"AS DEV"
  319.     ,"TS PM"
  320.     ,"TS DEV"
  321.     ,"TS TECH"
  322.     ,"BB EXEC"
  323.     ,"CHM"
  324.     ,"ANGEL Client Manager (CM)"
  325.     ,"ANGEL Engagement Team"
  326.     ,"BbOne Client Manager (CM)"
  327.     ,"Blackboard Executive"
  328.     ,"CE/Vista Hosting Mgr (ASM)"
  329.     ,"CE/Vista Tech Support (TSM)"
  330.     ,"Complex Hosting Manager (CHM)"
  331.     ,"Learn Client Manager (CM)"
  332.     ,"Learn Hosting Mgr (ASM)"
  333.     ,"Learn Reg Sales Mgr (RSM)"
  334.     ,"Learn Sales Engineer (SE)"
  335.     ,"Learn Services Developer"
  336.     ,"Learn Services ICM Mgr"
  337.     ,"Learn Services Project Manager"
  338.     ,"Learn Tech Support (TSM)"
  339.     ,"Transact Client Manager (CM)"
  340.     ,"Transact Project Manager"
  341.     ,"Transact Reg Acct Exec (RAE)"
  342.     ,"Transact Reg Sales Mgr (RSM)"
  343.     ,"Transact Sales Engineer (SE)"
  344.     ,"Transact Services Technician"
  345.     ,"Transact Tech Support (TSM)"
  346.     ,"Xythos Tech Support (TSM)"
  347. FROM CRMRPT..B_RPT_B_TEAM_ROLE;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!