Question about Insert

Question

Question about Insert

hawg

SSCertifiable

Points: 6044
More actions
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!
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011

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

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

Steve Henry-128072 SSC Veteran Points: 261 More actions · Answer 1

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

hawg SSCertifiable Points: 6044 More actions · Answer 2

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.

----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011