Bulk insert into a table

Navigation
0

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

  

ID_OIT_LET Id_OIT

     

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

     

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

     

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

     

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

     

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

In the source table, I have the ID_OIT and the fields to be populated, which is a date field and a varchar. As for the ID_OIT_LET I can do the insert three times to generate the values 1,2 and 3. My question is to make a loop type in the source table, so that it goes by taking the value and inserting. And there's one more problem. if you already have that key in the database, then you should call Update PROC instead of INSERT. I would like some help, to show me which way to go. I have no idea how. I hope it was clear.

I made this insert, as my colleague José Diz advised 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 testing), but if the table has information, which in the case has 8068 records, it does not add anything and gives me this message:

  

(3 affected row (s))

     

(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 can not 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?

    
asked by anonymous 20.04.2017 / 14:18

1 answer

1

A possible solution (without holding syntax)

In a protected block 

BEGIN TRY  
    INSERT INTO ....;  
END TRY  
BEGIN CATCH  
    --SE ERRO DE DUPLICADOS ENTAO
    UPDATE ....;
END CATCH;
    
20.04.2017 / 18:34
Error in AndroidManifest.xml 'class' or 'interface' excepted Force user to select an item in input type datalist