Results 1 to 4 of 4

Thread: Synchronize

  1. #1
    Join Date
    Jan 2004
    Posts
    164

    Unanswered: Synchronize

    I have two customer tables in SQL Server, but some of the field names are not the same, that I need to synchronize. Here is how they are structured: (Table 1 is old and Table 2 is new)

    Table 1 (900 records)
    ---------------
    cust_code
    cust_shipto_code
    cust_shipto_name
    cust_email



    Table 2 (700)
    ---------------

    custcode
    loccode
    custname
    custemail


    1. First I need to find out what records exist in Table 1 but not in Table 2 and add them to Table 2.

    2. Secondly, I need to then find all records in Table 1 that match the records in Table 2 and update the Email address in Table 2 with the data from Table 1.


    Any suggestions on how to do this sync?

  2. #2
    Join Date
    Oct 2003
    Location
    Newcastle, Australia
    Posts
    51
    How about

    insert into table2 (custcode, loccode, custname, custemail)
    select cust_code, cust_shipto_code, cust_shipto_name, cust_email
    from table1 where cust_code not in
    (select custcode from table2)

    I'm sure there are more efficient ways to do this but I think the above script should work.

  3. #3
    Join Date
    Jan 2004
    Posts
    164

    sync

    This is what I put in but I am getting an error when I run it.

    insert into [r2lprospector].[dbo].[orgloc](custcode, loccode, custshiptoname, custemail)
    select cust_code, cust_shipto_code, cust_shipto_name, cust_email
    from [cimpro1].[dbo].[OPCSHTO] where cust_code not in
    (select custcode from [r2lprospector].[dbo].[orgloc])


    The error is:

    Server: Msg 8152, Level 16, State 9, Line 1
    String or binary data would be truncated.
    The statement has been terminated.


    The error seems to be in the first line. Any suggestions?

  4. #4
    Join Date
    Oct 2003
    Location
    Newcastle, Australia
    Posts
    51

    Re: sync

    Originally posted by estefex
    This is what I put in but I am getting an error when I run it.

    insert into [r2lprospector].[dbo].[orgloc](custcode, loccode, custshiptoname, custemail)
    select cust_code, cust_shipto_code, cust_shipto_name, cust_email
    from [cimpro1].[dbo].[OPCSHTO] where cust_code not in
    (select custcode from [r2lprospector].[dbo].[orgloc])


    The error is:

    Server: Msg 8152, Level 16, State 9, Line 1
    String or binary data would be truncated.
    The statement has been terminated.


    The error seems to be in the first line. Any suggestions?
    There is at least one column in OPCSHTO that has a larger column size than the equivalent column in orgloc
    Increase the column length of the offending column(s) in orgloc

Posting Permissions

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