Bulk insert into a table

Question

I have two proc’s that my VB system already uses. One for Insert and one for Update. I need to use it now, directly in the database. The target table has a composite key, as follows. A field called ID_OIT_LET and another field called ID_OIT. The ID_OIT he is unique, never repeats. The ID_OIT_LET field, it only gets 1, 2 or 3. When it gets the three values, then I get 3 times the same value in ID_OIT, so:

ID_OIT_LET ID_OIT

---

1 ------------------ 39510

1 ------------------ 39511

1 ------------------ 39517

2 ------------------- 39517

3 ------------------- 39517

In the source table, I have ID_OIT and the fields to be populated, which is a date field and a varchar. As for ID_OIT_LET I can do the Insert three times to generate the values 1,2 and 3. My question is to make a kind loop in the source table, so that it goes taking the value and inserting. And there is one more problem. If that key already exists in the database, then the PROC should be called Update and not Insert. I would like a help, to show me which way to go. I have no idea how to do it. I hope I was clear.

I did this research, as the colleague José Diz guided me:

declare @UDT table (Num int);
INSERT into @UDT values (1), (2), (3);

SELECT T2.Num, T1.ID_OIT, T1.DT_RX, T1.RX_NUM
  from t_cmo_planilha_leitura as T1
       cross join @UDT as T2;

with cteComb as (
SELECT T2.Num, T1.ID_OIT, T1.DT_RX, T1.RX_NUM
  from t_cmo_planilha_leitura as T1
       cross join (values (1), (2), (3)) as T2 (Num)
)
MERGE
  into #t_cmo_oit1980_leitura_temp1 as D
  using cteComb as O
  on D.ID_OIT_LET = O.Num and D.ID_OIT = O.ID_OIT
  when matched then
       UPDATE set DT_RX= O.DT_RX, RX_NUM= O.RX_NUM
  when not matched by target then
       INSERT (ID_OIT_LET, ID_OIT, DT_RX, RX_NUM)
         values (O.Num, O.ID_OIT, O.DT_RX, O.RX_NUM) 
;

What happens is that, if the table is empty, it normally inserts (I did with a temp for tests), but if the table has information, which in the case has 8068 records, does not add anything and gives me this message:

(3 line(s) affected)

(3027 line(s) affected) Message 8672, Level 16, State 1, Line 104 The MERGE statement attempted to UPDATE or DELETE the same Row more than Once. This Happens when a target Row Matches more than one source Row. A MERGE statement cannot UPDATE/DELETE the same Row of the target table Multiple times. Refine the ON clause to ensure a target Row Matches at Most one source Row, or use the GROUP BY clause to group the source Rows.

How to proceed?

Answer 1

A possible solution (without clinging to syntax)

In a protected block

BEGIN TRY  
    INSERT INTO ....;  
END TRY  
BEGIN CATCH  
    --SE ERRO DE DUPLICADOS ENTAO
    UPDATE ....;
END CATCH;