MERGE missing records - better way?

[groleau]

table S (input) has columns A, B

table K (keep) renames them C, D and adds E

Need to add new records if not already there, leaving E null in that case.

Seems like there should be a simpler way than

INSERT INTO K (C, D)
SELECT A, B
FROM   S
WHERE NOT EXISTS (A, B)
      IN (SELECT C, D FROM K)

SQL cookbook tells about Oracle's MERGE but offers no workaround for the others


--
Wes Groleau

http://www.Parkview.com/

 

[gmmastros]

It's hard to tell, but I think you want to use this syntax. Try running the select first (without the insert) to make sure you are getting the right records.

[COLOR=blue]INSERT[/color] [COLOR=blue]INTO[/color] K (C, [COLOR=blue]D[/color])
[COLOR=blue]SELECT[/color] A, B
[COLOR=blue]FROM[/color]   S
       [COLOR=#FF00FF]Left[/color] [COLOR=blue]Join[/color] K
         [COLOR=blue]On[/color]  S.A = K.C
         And S.B = K.D
[COLOR=blue]WHERE[/color]  K.C [COLOR=blue]Is[/color] NULL

-George

"the screen with the little boxes in the window." - Moron

 

[groleau]

Wow, that was quick. I was about to post that maybe I should make a view to show the missing records, and then insert from that.

It's not a big enough data set to worry about efficiency.

thanks, your syntax is easier to read.

--
Wes Groleau

http://www.Parkview.com/