Post reply

Question about Insert

  • March 12, 2004 at 10:38 am

    #107784

    I know this is probably simple but I have just never done it and I'm having a hard time figuring it out.

    I have a two tables A and B.  Table A already contains data.  I populate Table B, using DTS, and now I want to do two things. 

    One, I want to update the records in Table A with the data in Table B for those records whose keys match.  (I have already gotten this to work.)

    Two, I want to add the records from Table B that don't already exist in Table A.

    I am doing this in an SP so I run the Update first, then I run the Insert.

    My question is, how do I write the Insert statement to know which records to insert?  I know I'm just missing something with my join.

    Thanks!

    ----------------------------------------------------------

    01010011010100010100110000100000010100110110010101110010011101100110010101110010
    001000000101001001101111011000110110101101110011

  • March 12, 2004 at 11:02 am

    #498562

    I find it hard to do the insert with a join, since the records don't exist in table a.  I do it this way:

    insert into tablea select mycolumnlist from tableb where tableb.keycolumns not in (select tablea.keycolumns from tablea)

    An alternate method is to use an outer join, and using the where clause where tablea.keycolumns is null.

    Steve

  • March 12, 2004 at 11:10 am

    #498566

    Thanks Steve.  Because of the types of (multiple) fields I have that make up the PK, the Outer Join option is what I was looking for.

    I wrote it that way and it works great. 

    Thanks again.

    ----------------------------------------------------------

    01010011010100010100110000100000010100110110010101110010011101100110010101110010
    001000000101001001101111011000110110101101110011

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

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