copying data from partition and converting
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: copying data from partition and converting

  1. #1
    Join Date
    Jul 2006
    Posts
    195

    copying data from partition and converting

    I am looking to copy data from one partition to another for testing purposes.

    My table is partitioned on create_date so I would like convert the old
    create_date values (2011-05-20) to a new values (2011-05-21) so it
    goes into another parition. In addition, I want is there any easy way to
    convert null values to a default value.

    Can this be done via a SQL statement? Is so can somebody provide and example or point me to the correct place in the documentation.

    Thanks

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by BeefStu View Post
    I am looking to copy data from one partition to another for testing purposes.

    My table is partitioned on create_date so I would like convert the old
    create_date values (2011-05-20) to a new values (2011-05-21) so it
    goes into another parition.
    A simple insert statement will do it, be sure you populate your insert statement - probably with an append hint - from souce partition, like FROM mytable PARTITION (mysourcepartition)

    In regards to adding one day to the date you can always use (mydate + 1) syntax.


    Quote Originally Posted by BeefStu View Post
    I want is there any easy way to
    convert null values to a default value.
    Research NVL() function, like nvl(mycolumn, defaul-value)
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Jul 2006
    Posts
    195
    For those of you who are intersted here is the solution. Also in the where
    clause you can use from paritition (partition_name)

    Set's create_date to 2011-05-28

    INSERT INTO XXX.YYY
    SELECT to_date( '2011-05-28' || to_char( create_date,'HH24:MI SS'), 'YYYY-MM-DD HH24:MI SS' ),
    col1, col2, col3, col4 FROM
    XXX.YYY where create_date >= TO_DATE ( '2011/02/17', 'yyyy/mm/dd') AND
    create_date < TO_DATE ( '2011/02/18', 'yyyy/mm/dd');

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width