Post reply

Data migration from Sybase to MS SQL Server

  • July 11, 2005 at 5:06 am

    #90150

    Hi all

    i am not quite sure if this is the right forum to ask this:

    i have two databases, one is Sybase 11 (say A) and the other is Ms sql server 2000 (say B).

    i have the table tbl_customer in both databases with different fields and data but with the same primary key:cust_id varchar(10)

    i have to merge the data and bring everything in B database from the A database.In A database the cust_id value comprizes from

    'CR'+'unique id', while in the B database is 'CU'+'unique id'.the problem is that i have the same customers in both databases (same last name, same first name...) wih different cust_id (i.e cust_id = 'CR00000001' and cust_id = 'CU00000009').

    How can i merge tha data or in general is there a strategy to follow? convert the 'CR' ids in CU ids and in what way? Insert the 'CR' in the B database along with the 'CU'? dont know what to do 

    i have a major problem cause the rest of my tables have the cust_id as a part of their pk

    need help. thanks in advance

     

  • July 12, 2005 at 12:15 am

    #573229

    I suggest that it will be cleaner if you add the other ID column to the destination table, then perform an Update using the other criteria (you mentioned same last name, same first name...). This approach allows you to check the process to look for dupes, etc. before processing the child data. Plus it can be used to Join with, for the child tables you mention.

    As an Option, you could create a temporary table to match both IDs and not affect the DDL for your destination table at all.

    Once you have completed the migration, then delete the temporary ID (column or table).

    Andy

  • July 12, 2005 at 1:57 am

    #573255

    Why you are not using a temp table corresponding to the structure of your Sybase data? So you could import the data from Sybase (via bcp, DTS...) into the temp table, them make the corresonding transormation and insert the correct data into your final table.



    Bye
    Gabor

  • July 13, 2005 at 4:39 am

    #573814

    thanks for the replies

    the problem is that probably i would have the same customer with a different cust_id.(by same i mean same last name, same fname....). For example

     when i will try to search for a customer by lname which one we choose? of course this is more business oriented question and i shouldnt ask you.

    thanks anyway

    PS the cust_id column is a part of the pk of half of my tables in both systems..... 

     

     

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply