-- 스토어드 sp_testnsert  프로시저 생성

Create Proc sp_testnsert (
                                               @A char(12),
                                               @B char(10),
                                               @C numeric(8,1),
                                               @D char(1),
                                               @E char(1)
)
WITH RECOMPILE
AS

   DECLARE @cur_sido char(2)
 
   SELECT @cur_sido=id FROM tb_cursido
   
   IF @cur_sido = '00'
   BEGIN
 
             INSERT INTO tob_kow_qty_rain10(
                                                               Ac,
                                                               Bc,
                                                               Cc,
                                                               Dc,
                                                               Ec,
                                                               Fc
            ) VALUES (
                                                               @A,
                                                               @B,
                                                               convert(numeric(8,1), @C),
                                                               @D,
                                                               @E,
                                                               getdate()
             )
 
   END
       
   IF @cur_sido = substring(@B, 1, 2)
   BEGIN
 
            INSERT INTO tob_kow_qty_rain10(
                                                               Ac,
                                                               Bc,
                                                               Cc,
                                                               Dc,
                                                               Ec,
                                                               Fc
            ) VALUES (
                                                               @A,
                                                               @B,
                                                               convert(numeric(8,1), @C),
                                                               @D,
                                                               @E,
                                                               getdate()
            )
 
   END
  
  

위 프로시저를 아래와 같은 형식으로 200번 이상 루프를 돌려 실행시킨 뒤 마지막에 commit을 실행합니다.


즉  루프를 돌게되면

sp_procxmode sp_testInsert, chained                           

exec sp_testInsert 1, '11', 1, 1, 1, 1



sp_procxmode sp_testInsert, chained                           

exec sp_testInsert 2, '47', 2, 2, 2, 2



sp_procxmode sp_testInsert, chained                           

exec sp_testInsert 3, '47', 3, 3, 3, 3

.
.
.


sp_procxmode sp_testInsert, chained                           

exec sp_testInsert 200, '48', 200, 200, 200, 200


이렇게 실행 명령을 내리고  commit 이 실행됩니다.




위와 같이 실행을 하면 db에 부하가 너무 걸리고 lock이 자꾸 걸려서 다른 테이블에도 영향을 미칩니다.

혹시 잘못된 부분이 있으면 가르쳐 주세요.