Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2012
    Posts
    8

    Question Unanswered: Migration of data using views

    I am working with Sybase ASE 12.5.4 version. I need to migrate data from one db to another db. First one is the older version and the second one is the newer version with some modification in the tables (columns rearranged and some new tables also).
    I need to migrate the data from the older version to the newer version.. means I have to reproduce the data whichever is there in the older version in terms of the newer one. I have created views from the older one modifying the data and have reproduced it in the new format. Now how do I migrate this data from the older version db to the new db? What all should I consider before migrating? What about the triggers and the constraints that are present in the new db?

  2. #2
    Join Date
    Jan 2012
    Location
    Lisbon
    Posts
    115
    Provided Answers: 1
    Hello,

    Yes its possible.
    So i assume that the old table and new table are diferent. So you can't use dump wich replicates the schema.

    So you have an option bcp out from old server and bcp in on new. You will have to this for each table. Yes you can bcp out from a view.

    Code:
    bcp [[database_name.]owner.]table_name [: [ partition_id | slice_number ] |
        partition partition_name] {in | out} datafile
    The 'table' argument can be a view.

    Hope it helps.

  3. #3
    Join Date
    Oct 2012
    Posts
    8
    hi,

    Thanks for the reply.
    I have just created the views in the older version.. meaning I just have the data in the rearranged format as per the new version.
    As per my understanding, first I need to delete the tables from the new db and I need to create tables in the new db as per the modified table structure and then bcp out the data from the views in the old db to a flat file and then bcp in the data fromt the flat file to the tables created in the new db.
    Now after this step I will have the data in the tables but can I do this before defining the constraints and triggers in the new db?
    If no, how should I create these?
    Are the triggers defined on the db itself? If yes then it will have dependency on the tables in that db.. so should i delete these and modify as per my new tables? how should I do that?

    Thanks in advance.

  4. #4
    Join Date
    Jan 2012
    Location
    Lisbon
    Posts
    115
    Provided Answers: 1
    Hey,
    Okay, the bcp will be faster withou any constraints or PKs or triggers. So you can delete them form the destination table.
    Then do bcp in.
    Then recreate them. How? If you have them already just save the code somewhere and then run it.
    You will only have to adjust if your trigger use columns that no longer exist.

    Notice if the tables are big, when you will recreate your keys and indexes may take a while, and you should look out for the transaction log not to fill.
    Same thing with bcp in, if its lots of data, use the batchsize parameter.

    Cheers

Tags for this Thread

Posting Permissions

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