none
sql server 2017 INSERT and UPDATE lock table RRS feed

  • Question

  • Hello friends,i have a database with table of millions of records.I update my database with INSERT or UPDATE from file JSON but i have one problem:

    while I update/insert my tables users are blocked;

    this operation must always be performed while the db is in use.

    DECLARE @json VARCHAR(max) 
    DECLARE @Sql  nVARCHAR(max)
    DECLARE @sLOG bigint
    DECLARE @LOG bigint
    
    		DECLARE @INIZIO DATETIME
    
    		DECLARE @PruUpdate       TABLE (
    										upd_KCODDLO VARCHAR(3), upd_KCODPRU BIGINT, upd_ADESCRI varchar(50), upd_DATAUSC varchar(10),
    										upd_NUMUSC int, upd_QSCORTA int, upd_ABARCODE varchar(18), upd_ASOTTO varchar(50), upd_IPRZCOP float,
    										upd_PERCDEF float, upd_NPESO float, upd_NCOMPRV float, upd_KCODLVR varchar(1), upd_KCODEDT int,
    										upd_KCODPER int, upd_KCODTPR int, upd_KCODMIT int, upd_NCODTES int, upd_NCODVAR int, 
    										upd_NCODDIF int, upd_KCODRIV int, upd_PERCSCB float, upd_IPRZNET float,
    										PRIMARY KEY (upd_KCODDLO asc, upd_KCODPRU asc, upd_KCODRIV asc)
    										)
    
    		SELECT @json = BulkColumn FROM OPENROWSET(BULK'D:\ftp\LocalUser\json\sped\20902PRU008257_20190520150410.json', SINGLE_BLOB) JSON;
    		--SELECT @json = BulkColumn FROM OPENROWSET(BULK'D:\smartedi\20902PRU008414_20190514104915.json', SINGLE_BLOB) JSON;
    		--SELECT @json
    
    BEGIN -- START UPDATE sma_PROD_USCITE --
    															INSERT INTO @PruUpdate 
    																SELECT 																			
    																		'209',
    																		JSON_Value (c.value, '$.PRU_KCODPRU') as PRU_KCODPRU, 
    																		JSON_Value (c.value, '$.PRU_ADESCRI') as PRU_ADESCRI, 
    																		JSON_Value (c.value, '$.PRU_DATAUSC') as PRU_DATAUSC, 
    																		JSON_Value (c.value, '$.PRU_NUMUSC')  as PRU_NUMUSC,
    																		JSON_Value (c.value, '$.PRU_QSCORTA')  as PRU_QSCORTA,
    																		JSON_Value (c.value, '$.PRU_ABARCODE')  as PRU_ABARCODE,
    																		JSON_Value (c.value, '$.PRU_ASOTTO')  as PRU_ASOTTO,
    																		JSON_Value (c.value, '$.PRU_IPRZCOP')  as PRU_IPRZCOP,
    																		JSON_Value (c.value, '$.PRU_PERCDEF')  as PRU_PERCDEF,
    																		JSON_Value (c.value, '$.PRU_NPESO')  as PRU_NPESO,
    																		JSON_Value (c.value, '$.PRU_NCOMPRV')  as PRU_NCOMPRV,
    																		JSON_Value (c.value, '$.PRU_KCODLVR')  as PRU_KCODLVR,
    																		JSON_Value (c.value, '$.PRU_KCODEDT')  as PRU_KCODEDT,
    																		JSON_Value (c.value, '$.PRU_KCODPER')  as PRU_KCODPER,
    																		JSON_Value (c.value, '$.PRU_KCODTPR')  as PRU_KCODTPR,
    																		JSON_Value (c.value, '$.PRU_KCODMIT')  as PRU_KCODMIT,
    																		JSON_Value (c.value, '$.PRU_NCODTES')  as PRU_NCODTES,
    																		JSON_Value (c.value, '$.PRU_NCODVAR')  as PRU_NCODVAR,
    																		JSON_Value (c.value, '$.PRU_NCODDIF')  as PRU_NCODDIF,
    																		JSON_Value (p.value, '$.PRU_KCODRIV') as PRU_KCODRIV, 
    																		JSON_Value (p.value, '$.PRU_NPERSCO') as PRU_NPERSCO,
    																		JSON_Value (p.value, '$.PRU_IPRZNET') as PRU_IPRZNET							
    																FROM OPENJSON (@json, '$.HEADER') as c
    																CROSS APPLY OPENJSON (c.value, '$.PRU_KCODRIV') as p
    														END;
    																										
    														BEGIN
    															UPDATE sma_PROD_USCITE
    																SET
    																	PRU_ADESCRI  = upd_ADESCRI,  
    																	PRU_DATAUSC  = CONVERT(date, upd_DATAUSC , 104) ,
    																	PRU_NUMUSC   = upd_NUMUSC,  
    																	PRU_QSCORTA  = upd_QSCORTA, 
    																	PRU_ABARCODE = upd_ABARCODE, 
    																	PRU_ASOTTO   = upd_ASOTTO, 
    																	PRU_IPRZCOP  = upd_IPRZCOP,
    																	PRU_PERCDEF  = upd_PERCDEF, 
    																	PRU_NPESO    = upd_NPESO,   
    																	PRU_NCOMPRV  = upd_NCOMPRV,  
    																	PRU_KCODLVR  = upd_KCODLVR, 
    																	PRU_KCODEDT  = upd_KCODEDT,
    																	PRU_KCODPER  = upd_KCODPER, 
    																	PRU_KCODTPR  = upd_KCODTPR, 
    																	PRU_KCODMIT  = upd_KCODMIT,  
    																	PRU_NCODTES  = upd_NCODTES, 
    																	PRU_NCODVAR  = upd_NCODVAR,
    																	PRU_NCODDIF  = upd_NCODDIF, 
    																	PRU_PERCSCB  = upd_PERCSCB,  
    																	PRU_IPRZNET  = upd_IPRZNET
    																FROM
    																	sma_PROD_USCITE
    																	INNER JOIN @PruUpdate
    																		ON PRU_KCODDLO = upd_KCODDLO
    																		AND PRU_KCODPRU = upd_KCODPRU
    																		and PRU_KCODRIV = upd_KCODRIV
    														END;-- END UPDATE sma_PROD_USCITE --
    														----START INSERT LOG--
    												
    
    
    														BEGIN -- START INSERT sma_PROD_USCITE --
    															INSERT INTO sma_PROD_USCITE (
    																						PRU_KCODDLO, PRU_KCODPRU, PRU_ADESCRI,  PRU_DATAUSC,
    																						PRU_NUMUSC,  PRU_QSCORTA, PRU_ABARCODE, PRU_ASOTTO, PRU_IPRZCOP,
    																						PRU_PERCDEF, PRU_NPESO,   PRU_NCOMPRV,  PRU_KCODLVR, PRU_KCODEDT,
    																						PRU_KCODPER, PRU_KCODTPR, PRU_KCODMIT,  PRU_NCODTES, PRU_NCODVAR,
    																						PRU_NCODDIF, PRU_KCODRIV, PRU_PERCSCB,  PRU_IPRZNET, PRU_ABARCODEPV 																																																
    																						)
    																					SELECT
    																						'209',
    																						JSON_Value (c.value, '$.PRU_KCODPRU') as PRU_KCODPRU, 
    																						JSON_Value (c.value, '$.PRU_ADESCRI') as PRU_ADESCRI, 
    																						CONVERT(date, JSON_Value (c.value, '$.PRU_DATAUSC') , 104) as PRU_DATAUSC,
    																						JSON_Value (c.value, '$.PRU_NUMUSC')  as PRU_NUMUSC,
    																						JSON_Value (c.value, '$.PRU_QSCORTA')  as PRU_QSCORTA,
    																						JSON_Value (c.value, '$.PRU_ABARCODE')  as PRU_ABARCODE,
    																						JSON_Value (c.value, '$.PRU_ASOTTO')  as PRU_ASOTTO,
    																						JSON_Value (c.value, '$.PRU_IPRZCOP')  as PRU_IPRZCOP,
    																						JSON_Value (c.value, '$.PRU_PERCDEF')  as PRU_PERCDEF,
    																						JSON_Value (c.value, '$.PRU_NPESO')  as PRU_NPESO,
    																						JSON_Value (c.value, '$.PRU_NCOMPRV')  as PRU_NCOMPRV,
    																						JSON_Value (c.value, '$.PRU_KCODLVR')  as PRU_KCODLVR,
    																						JSON_Value (c.value, '$.PRU_KCODEDT')  as PRU_KCODEDT,
    																						JSON_Value (c.value, '$.PRU_KCODPER')  as PRU_KCODPER,
    																						JSON_Value (c.value, '$.PRU_KCODTPR')  as PRU_KCODTPR,
    																						JSON_Value (c.value, '$.PRU_KCODMIT')  as PRU_KCODMIT,
    																						JSON_Value (c.value, '$.PRU_NCODTES')  as PRU_NCODTES,
    																						JSON_Value (c.value, '$.PRU_NCODVAR')  as PRU_NCODVAR,
    																						JSON_Value (c.value, '$.PRU_NCODDIF')  as PRU_NCODDIF,
    																						JSON_Value (p.value, '$.PRU_KCODRIV') as PRU_KCODRIV, 
    																						JSON_Value (p.value, '$.PRU_NPERSCO') as PRU_NPERSCO,
    																						JSON_Value (p.value, '$.PRU_IPRZNET') as PRU_IPRZNET,
    																						JSON_Value (c.value, '$.PRU_ABARCODE')  as PRU_ABARCODE
    																						FROM OPENJSON (@json, '$.HEADER') as c
    																						CROSS APPLY OPENJSON (c.value, '$.PRU_KCODRIV') as p											
    																						WHERE NOT EXISTS 
    																										(SELECT 1
    																											FROM sma_PROD_USCITE with (updlock, rowlock, holdlock)
    																											WHERE PRU_KCODDLO = '209'
    																												and PRU_KCODPRU = JSON_Value (c.value, '$.PRU_KCODPRU')
    																												and PRU_KCODRIV = JSON_Value (p.value, '$.PRU_KCODRIV')
    																										)
    																									;													
    														END-- END INSERT sma_PROD_USCITE --
    I also tried this way but without success:
    DECLARE @json VARCHAR(max) 
    DECLARE @Sql  nVARCHAR(max)
    
    DECLARE @Cupd_KCODDLO VARCHAR(3)
    DECLARE @Cupd_KCODPRU BIGINT
    DECLARE @Cupd_ADESCRI varchar(50)
    DECLARE @Cupd_DATAUSC varchar(10)
    DECLARE @Cupd_NUMUSC int
    DECLARE @Cupd_QSCORTA int
    DECLARE @Cupd_ABARCODE varchar(18)
    DECLARE @Cupd_ASOTTO varchar(50)
    DECLARE @Cupd_IPRZCOP float
    DECLARE @Cupd_PERCDEF float
    DECLARE @Cupd_NPESO float
    DECLARE @Cupd_NCOMPRV float
    DECLARE @Cupd_KCODLVR varchar(1)
    DECLARE @Cupd_KCODEDT int
    DECLARE @Cupd_KCODPER int
    DECLARE @Cupd_KCODTPR int
    DECLARE @Cupd_KCODMIT int
    DECLARE @Cupd_NCODTES int
    DECLARE @Cupd_NCODVAR int
    DECLARE @Cupd_NCODDIF int
    DECLARE @Cupd_KCODRIV int
    DECLARE @Cupd_PERCSCB float
    DECLARE @Cupd_IPRZNET float
    
    DECLARE @INIZIO DATETIME
    DECLARE @contoINS int
    DECLARE @contoUPD int
    DECLARE @LOG bigint
    DECLARE @sLOG bigint
    
    		DECLARE @PruUpdate       TABLE (
    										upd_KCODDLO VARCHAR(3), upd_KCODPRU BIGINT, upd_ADESCRI varchar(50), upd_DATAUSC varchar(10),
    										upd_NUMUSC int, upd_QSCORTA int, upd_ABARCODE varchar(18), upd_ASOTTO varchar(50), upd_IPRZCOP float,
    										upd_PERCDEF float, upd_NPESO float, upd_NCOMPRV float, upd_KCODLVR varchar(1), upd_KCODEDT int,
    										upd_KCODPER int, upd_KCODTPR int, upd_KCODMIT int, upd_NCODTES int, upd_NCODVAR int, 
    										upd_NCODDIF int, upd_KCODRIV int, upd_PERCSCB float, upd_IPRZNET float
    										)
    DECLARE mycur1 CURSOR for
    select upd_KCODDLO , upd_KCODPRU , upd_ADESCRI , upd_DATAUSC ,
    		upd_NUMUSC , upd_QSCORTA , upd_ABARCODE , upd_ASOTTO , upd_IPRZCOP ,
    		upd_PERCDEF , upd_NPESO , upd_NCOMPRV , upd_KCODLVR , upd_KCODEDT ,
    		upd_KCODPER , upd_KCODTPR , upd_KCODMIT , upd_NCODTES , upd_NCODVAR , 
    		upd_NCODDIF , upd_KCODRIV , upd_PERCSCB , upd_IPRZNET 
    from @PruUpdate
    
    		DECLARE @BodUpdate       TABLE (
    										upd_KCODDLO VARCHAR(3), upd_KCODBOS int, upd_KCODCAB int, upd_KCODPRU BIGINT, upd_KCODRIV INT, 
    										upd_QUANTIT int, upd_IPRZNET float, upd_NPRGSTA int, upd_PERCSCB float, upd_FCVEND varchar(1), 
    										upd_IPRZCV float
    										)
    --SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    		SELECT @json = BulkColumn FROM OPENROWSET(BULK'D:\ftp\LocalUser\json\sped\20902PRU007592_20190521121645.json', SINGLE_BLOB) JSON;
    		--SELECT @json
    set @contoINS = 0
    set @contoupd = 0
    set @sLOG = 0
    set @sLOG = (NEXT VALUE FOR sLOG)
    
    SET @INIZIO = GETDATE()
    
    BEGIN -- START UPDATE sma_PROD_USCITE --
    															INSERT INTO @PruUpdate 
    																SELECT 																			
    																		'209',
    																		JSON_Value (c.value, '$.PRU_KCODPRU') as PRU_KCODPRU, 
    																		JSON_Value (c.value, '$.PRU_ADESCRI') as PRU_ADESCRI, 
    																		JSON_Value (c.value, '$.PRU_DATAUSC') as PRU_DATAUSC, 
    																		JSON_Value (c.value, '$.PRU_NUMUSC')  as PRU_NUMUSC,
    																		JSON_Value (c.value, '$.PRU_QSCORTA')  as PRU_QSCORTA,
    																		JSON_Value (c.value, '$.PRU_ABARCODE')  as PRU_ABARCODE,
    																		JSON_Value (c.value, '$.PRU_ASOTTO')  as PRU_ASOTTO,
    																		JSON_Value (c.value, '$.PRU_IPRZCOP')  as PRU_IPRZCOP,
    																		JSON_Value (c.value, '$.PRU_PERCDEF')  as PRU_PERCDEF,
    																		JSON_Value (c.value, '$.PRU_NPESO')  as PRU_NPESO,
    																		JSON_Value (c.value, '$.PRU_NCOMPRV')  as PRU_NCOMPRV,
    																		JSON_Value (c.value, '$.PRU_KCODLVR')  as PRU_KCODLVR,
    																		JSON_Value (c.value, '$.PRU_KCODEDT')  as PRU_KCODEDT,
    																		JSON_Value (c.value, '$.PRU_KCODPER')  as PRU_KCODPER,
    																		JSON_Value (c.value, '$.PRU_KCODTPR')  as PRU_KCODTPR,
    																		JSON_Value (c.value, '$.PRU_KCODMIT')  as PRU_KCODMIT,
    																		JSON_Value (c.value, '$.PRU_NCODTES')  as PRU_NCODTES,
    																		JSON_Value (c.value, '$.PRU_NCODVAR')  as PRU_NCODVAR,
    																		JSON_Value (c.value, '$.PRU_NCODDIF')  as PRU_NCODDIF,
    																		JSON_Value (p.value, '$.PRU_KCODRIV') as PRU_KCODRIV, 
    																		JSON_Value (p.value, '$.PRU_NPERSCO') as PRU_NPERSCO,
    																		JSON_Value (p.value, '$.PRU_IPRZNET') as PRU_IPRZNET							
    																FROM OPENJSON (@json, '$.HEADER') as c
    																CROSS APPLY OPENJSON (c.value, '$.PRU_KCODRIV') as p
    														END;
    														set @LOG= @@ROWCOUNT
    														BEGIN
    															INSERT INTO sma_LOG_IMPORT
    																		(LOG_TIPOREC,LOG_BOS,LOG_NOMEFILE, LOG_NUMRECORD, LOG_NOTE,
    																			LOG_INIZIO,LOG_FINE, LOG_TEMPOEXEC, LOG_SEQUENCE)
    																	VALUES
    																		('PRU', '0', '', @LOG, 'INSERT TMP',
    																		@INIZIO, GetDate(), DATEDIFF(MILLISECOND, @INIZIO, GETDATE()), @sLOG)
    														END																									
    
    SET @INIZIO = GETDATE()
    OPEN mycur1
    
     FETCH NEXT FROM mycur1 
    		INTO @Cupd_KCODDLO, @Cupd_KCODPRU, @Cupd_ADESCRI ,@Cupd_DATAUSC ,
    			 @Cupd_NUMUSC , @Cupd_QSCORTA , @Cupd_ABARCODE , @Cupd_ASOTTO ,
    			 @Cupd_IPRZCOP , @Cupd_PERCDEF , @Cupd_NPESO , @Cupd_NCOMPRV, 
    			 @Cupd_KCODLVR , @Cupd_KCODEDT , @Cupd_KCODPER , @Cupd_KCODTPR ,
    			 @Cupd_KCODMIT , @Cupd_NCODTES , @Cupd_NCODVAR , @Cupd_NCODDIF ,
    			 @Cupd_KCODRIV , @Cupd_PERCSCB ,  @Cupd_IPRZNET 
    
     WHILE @@Fetch_Status = 0
     BEGIN
    
     --begin tran /* default read committed isolation level is fine */
    	 
    
    	 if not exists (select 's' from sma_PROD_USCITE WITH (ROWLOCK) --with (updlock, rowlock, holdlock)
    						 where PRU_KCODDLO=@Cupd_KCODDLO
    							and PRU_KCODRIV=@Cupd_KCODRIV
    							AND PRU_KCODPRU=@Cupd_KCODPRU )
    
    			BEGIN -- START INSERT sma_PROD_USCITE --
    				set @contoINS = @contoINS +1;
    				INSERT INTO sma_PROD_USCITE  WITH (ROWLOCK) (
    											PRU_KCODDLO, PRU_KCODPRU, PRU_ADESCRI,  PRU_DATAUSC,
    											PRU_NUMUSC,  PRU_QSCORTA, PRU_ABARCODE, PRU_ASOTTO, PRU_IPRZCOP,
    											PRU_PERCDEF, PRU_NPESO,   PRU_NCOMPRV,  PRU_KCODLVR, PRU_KCODEDT,
    											PRU_KCODPER, PRU_KCODTPR, PRU_KCODMIT,  PRU_NCODTES, PRU_NCODVAR,
    											PRU_NCODDIF, PRU_KCODRIV, PRU_PERCSCB,  PRU_IPRZNET, PRU_ABARCODEPV 																																																
    											)values
    											(
    											@Cupd_KCODDLO, @Cupd_KCODPRU, @Cupd_ADESCRI ,CONVERT(date, @Cupd_DATAUSC , 104)  ,
    											@Cupd_NUMUSC , @Cupd_QSCORTA , @Cupd_ABARCODE , @Cupd_ASOTTO ,@Cupd_IPRZCOP,
    											@Cupd_PERCDEF , @Cupd_NPESO , @Cupd_NCOMPRV, @Cupd_KCODLVR , @Cupd_KCODEDT ,
    											@Cupd_KCODPER , @Cupd_KCODTPR ,@Cupd_KCODMIT , @Cupd_NCODTES , @Cupd_NCODVAR ,
    											@Cupd_NCODDIF ,@Cupd_KCODRIV , @Cupd_PERCSCB ,  @Cupd_IPRZNET,@Cupd_ABARCODE
    											)  																											
    			END-- END INSERT sma_PROD_USCITE --
    	 else
    			BEGIN -- START UPDATE sma_PROD_USCITE --
    				set @contoUPD = @contoUPD +1;
    				UPDATE sma_PROD_USCITE  WITH (ROWLOCK)
    										SET
    											PRU_ADESCRI  = @Cupd_ADESCRI,  
    											PRU_DATAUSC  = CONVERT(date, @Cupd_DATAUSC , 104) ,
    											PRU_NUMUSC   = @Cupd_NUMUSC,  
    											PRU_QSCORTA  = @Cupd_QSCORTA, 
    											PRU_ABARCODE = @Cupd_ABARCODE, 
    											PRU_ASOTTO   = @Cupd_ASOTTO, 
    											PRU_IPRZCOP  = @Cupd_IPRZCOP,
    											PRU_PERCDEF  = @Cupd_PERCDEF, 
    											PRU_NPESO    = @Cupd_NPESO,   
    											PRU_NCOMPRV  = @Cupd_NCOMPRV,  
    											PRU_KCODLVR  = @Cupd_KCODLVR, 
    											PRU_KCODEDT  = @Cupd_KCODEDT,
    											PRU_KCODPER  = @Cupd_KCODPER, 
    											PRU_KCODTPR  = @Cupd_KCODTPR, 
    											PRU_KCODMIT  = @Cupd_KCODMIT,  
    											PRU_NCODTES  = @Cupd_NCODTES, 
    											PRU_NCODVAR  = @Cupd_NCODVAR,
    											PRU_NCODDIF  = @Cupd_NCODDIF, 
    											PRU_PERCSCB  = @Cupd_PERCSCB,  
    											PRU_IPRZNET  = @Cupd_IPRZNET		
    							where PRU_KCODDLO=@Cupd_KCODDLO
    								and PRU_KCODRIV=@Cupd_KCODRIV
    								AND PRU_KCODPRU=@Cupd_KCODPRU 																																			
    			END-- END UPDATE sma_PROD_USCITE --
    
    
    	 --commit /* locks are released here */
    
    	 FETCH NEXT FROM mycur1 
    			INTO @Cupd_KCODDLO, @Cupd_KCODPRU, @Cupd_ADESCRI ,@Cupd_DATAUSC ,
    				 @Cupd_NUMUSC , @Cupd_QSCORTA , @Cupd_ABARCODE , @Cupd_ASOTTO ,
    				 @Cupd_IPRZCOP , @Cupd_PERCDEF , @Cupd_NPESO , @Cupd_NCOMPRV, 
    				 @Cupd_KCODLVR , @Cupd_KCODEDT , @Cupd_KCODPER , @Cupd_KCODTPR ,
    				 @Cupd_KCODMIT , @Cupd_NCODTES , @Cupd_NCODVAR , @Cupd_NCODDIF ,
    				 @Cupd_KCODRIV , @Cupd_PERCSCB ,  @Cupd_IPRZNET 
    
    
     END
    
     CLOSE mycur1
     DEALLOCATE mycur1

    please help me


    Emanuele


    Tuesday, May 21, 2019 1:31 PM

Answers

  • Hi, so with the MERGE is better/fast?

    Is that a question or an observation?

    I don't knwo anything about your system, so I can't say exactly what will work for you. What I noted in my previous message is that the cursor solution should not cause any problem - unless it is wrapped in a transaction that I don't see, but which you have, for instance in your client code.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, May 23, 2019 9:00 PM

All replies

  • Emanuele,

    Large ETL jobs like that are almost always going to interfere with ongoing operations.

    You can try to make things faster, and we'd have to see your table and index definitions for that, and then look at the execution plans for your code.  Maybe better indexing could speed operations and reduce blocking significantly.

    Another thing to try is to do the imports in batches.  Do a few hundred or a few thousand rows, then waitfor a second, then continue.  You still get blocking but just for shorter times.

    Josh

    Tuesday, May 21, 2019 3:54 PM
  • Can you post the CREATE TABLE and CREATE INDEX statement for the table sma_PROD_USCITE?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, May 21, 2019 9:26 PM
  • ok:
    CREATE TABLE [dbo].[sma_PROD_USCITE](
    	[PRU_KCODDLO] [varchar](3) NOT NULL,
    	[PRU_KCODPRU] [bigint] NOT NULL,
    	[PRU_DATAUSC] [date] NULL,
    	[PRU_NUMUSC] [int] NULL,
    	[PRU_QSCORTA] [int] NULL,
    	[PRU_ABARCODE] [varchar](18) NULL,
    	[PRU_ASOTTO] [varchar](50) NULL,
    	[PRU_IPRZCOP] [float] NULL,
    	[PRU_PERCDEF] [float] NULL,
    	[PRU_PERCSCB] [float] NULL,
    	[PRU_NALTEZZ] [float] NULL,
    	[PRU_NLARGHE] [float] NULL,
    	[PRU_NSPESSO] [float] NULL,
    	[PRU_NPESO] [float] NULL,
    	[PRU_NCOMPRV] [float] NULL,
    	[PRU_KCODLVR] [varchar](1) NULL,
    	[PRU_ADESDIF] [varchar](200) NULL,
    	[PRU_ADESCRI] [varchar](50) NULL,
    	[PRU_KCODEDT] [int] NULL,
    	[PRU_KCODPER] [int] NULL,
    	[PRU_KCODTPR] [int] NULL,
    	[PRU_KCODMIT] [int] NULL,
    	[PRU_NCODTES] [int] NULL,
    	[PRU_NCODVAR] [int] NULL,
    	[PRU_KCODASE] [varchar](1) NULL,
    	[PRU_NCODDIF] [int] NULL,
    	[PRU_KCODRIV] [int] NOT NULL,
    	[PRU_IPRZNET] [float] NULL,
    	[PRU_ANOTE] [varchar](200) NULL,
    	[PRU_ABARCODEPV] [varchar](18) NULL,
    	[PRU_RIFERIMENTO] [varchar](200) NULL,
    	[PRU_MANCECC] [int] NULL,
    	[PRU_ARRIVODL] [int] NULL,
    	[PRU_ARRIVOPV] [int] NULL,
    	[PRU_RESODL] [int] NULL,
    	[PRU_RESOPV] [int] NULL,
    	[PRU_DATAURR] [date] NULL,
    	[PRU_KCODFOR] [numeric](18, 0) NULL,
    	[PRU_QRIFTOT] [int] NULL,
    	[PRU_QLIVCOR] [int] NULL,
    	[PRU_IPRZCOPPV] [float] NULL,
    	[PRU_CORRPV] [int] NULL,
    	[PRU_AAMS] [varchar](10) NULL,
    	[PRU_PRZACQ] [float] NULL,
    	[PRU_AGGIO] [float] NULL,
    	[PRU_IVA] [int] NULL,
     CONSTRAINT [PK_sma_PROD_USCITE] PRIMARY KEY CLUSTERED 
    (
    	[PRU_KCODDLO] ASC,
    	[PRU_KCODPRU] ASC,
    	[PRU_KCODRIV] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    
    ALTER TABLE [dbo].[sma_PROD_USCITE] ADD  CONSTRAINT [DF_sma_PROD_USCITE_PRU_PRZACQ]  DEFAULT ((0)) FOR [PRU_PRZACQ]
    GO
    
    ALTER TABLE [dbo].[sma_PROD_USCITE] ADD  CONSTRAINT [DF_sma_PROD_USCITE_PRU_AGGIO]  DEFAULT ((0)) FOR [PRU_AGGIO]
    GO
    
    
    

    and index:
    /****** Object:  Index [_dta_index_sma_PROD_USCITE_5_1358627883__K1_K27_K18_K2_K3_K4_6_7_8_33_34_36_39_40_41_42]    Script Date: 22/05/2019 07:25:28 ******/
    CREATE NONCLUSTERED INDEX [_dta_index_sma_PROD_USCITE_5_1358627883__K1_K27_K18_K2_K3_K4_6_7_8_33_34_36_39_40_41_42] ON [dbo].[sma_PROD_USCITE]
    (
    	[PRU_KCODDLO] ASC,
    	[PRU_KCODRIV] ASC,
    	[PRU_ADESCRI] ASC,
    	[PRU_KCODPRU] ASC,
    	[PRU_DATAUSC] ASC,
    	[PRU_NUMUSC] ASC
    )
    INCLUDE ( 	[PRU_ABARCODE],
    	[PRU_ASOTTO],
    	[PRU_IPRZCOP],
    	[PRU_ARRIVODL],
    	[PRU_ARRIVOPV],
    	[PRU_RESOPV],
    	[PRU_QRIFTOT],
    	[PRU_QLIVCOR],
    	[PRU_IPRZCOPPV],
    	[PRU_CORRPV]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO
    
    
    /****** Object:  Index [_dta_index_sma_PROD_USCITE_5_1358627883__K1_K27_K2_K18_K7_K31_K3_K4_6_8_41]    Script Date: 22/05/2019 07:25:39 ******/
    CREATE NONCLUSTERED INDEX [_dta_index_sma_PROD_USCITE_5_1358627883__K1_K27_K2_K18_K7_K31_K3_K4_6_8_41] ON [dbo].[sma_PROD_USCITE]
    (
    	[PRU_KCODDLO] ASC,
    	[PRU_KCODRIV] ASC,
    	[PRU_KCODPRU] ASC,
    	[PRU_ADESCRI] ASC,
    	[PRU_ASOTTO] ASC,
    	[PRU_RIFERIMENTO] ASC,
    	[PRU_DATAUSC] ASC,
    	[PRU_NUMUSC] ASC
    )
    INCLUDE ( 	[PRU_ABARCODE],
    	[PRU_IPRZCOP],
    	[PRU_IPRZCOPPV]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO
    
    SET ANSI_PADDING ON
    GO
    
    /****** Object:  Index [_dta_index_sma_PROD_USCITE_5_1358627883__K1_K27_K2_K6_K30]    Script Date: 22/05/2019 07:25:53 ******/
    CREATE NONCLUSTERED INDEX [_dta_index_sma_PROD_USCITE_5_1358627883__K1_K27_K2_K6_K30] ON [dbo].[sma_PROD_USCITE]
    (
    	[PRU_KCODDLO] ASC,
    	[PRU_KCODRIV] ASC,
    	[PRU_KCODPRU] ASC,
    	[PRU_ABARCODE] ASC,
    	[PRU_ABARCODEPV] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO
    
    SET ANSI_PADDING ON
    GO
    
    /****** Object:  Index [_dta_index_sma_PROD_USCITE_5_1358627883__K2_K27_K1_31]    Script Date: 22/05/2019 07:26:01 ******/
    CREATE NONCLUSTERED INDEX [_dta_index_sma_PROD_USCITE_5_1358627883__K2_K27_K1_31] ON [dbo].[sma_PROD_USCITE]
    (
    	[PRU_KCODPRU] ASC,
    	[PRU_KCODRIV] ASC,
    	[PRU_KCODDLO] ASC
    )
    INCLUDE ( 	[PRU_RIFERIMENTO]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO
    
    SET ANSI_PADDING ON
    GO
    
    /****** Object:  Index [_dta_index_sma_PROD_USCITE_5_1358627883__K25]    Script Date: 22/05/2019 07:26:08 ******/
    CREATE NONCLUSTERED INDEX [_dta_index_sma_PROD_USCITE_5_1358627883__K25] ON [dbo].[sma_PROD_USCITE]
    (
    	[PRU_KCODASE] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO
    
    /****** Object:  Index [_dta_index_sma_PROD_USCITE_5_1358627883__K27]    Script Date: 22/05/2019 07:26:16 ******/
    CREATE NONCLUSTERED INDEX [_dta_index_sma_PROD_USCITE_5_1358627883__K27] ON [dbo].[sma_PROD_USCITE]
    (
    	[PRU_KCODRIV] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO
    
    
    SET ANSI_PADDING ON
    GO
    
    /****** Object:  Index [_dta_index_sma_PROD_USCITE_5_1358627883__K30_K6_K1_K27_K2_K18_K3_K4_7_8_33_34_36_39_40_41_42]    Script Date: 22/05/2019 07:26:25 ******/
    CREATE NONCLUSTERED INDEX [_dta_index_sma_PROD_USCITE_5_1358627883__K30_K6_K1_K27_K2_K18_K3_K4_7_8_33_34_36_39_40_41_42] ON [dbo].[sma_PROD_USCITE]
    (
    	[PRU_ABARCODEPV] ASC,
    	[PRU_ABARCODE] ASC,
    	[PRU_KCODDLO] ASC,
    	[PRU_KCODRIV] ASC,
    	[PRU_KCODPRU] ASC,
    	[PRU_ADESCRI] ASC,
    	[PRU_DATAUSC] ASC,
    	[PRU_NUMUSC] ASC
    )
    INCLUDE ( 	[PRU_ASOTTO],
    	[PRU_IPRZCOP],
    	[PRU_ARRIVODL],
    	[PRU_ARRIVOPV],
    	[PRU_RESOPV],
    	[PRU_QRIFTOT],
    	[PRU_QLIVCOR],
    	[PRU_IPRZCOPPV],
    	[PRU_CORRPV]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO
    
    SET ANSI_PADDING ON
    GO
    
    /****** Object:  Index [<Name of Missing Index, sysname,>]    Script Date: 22/05/2019 07:26:37 ******/
    CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>] ON [dbo].[sma_PROD_USCITE]
    (
    	[PRU_KCODDLO] ASC,
    	[PRU_NCODDIF] ASC,
    	[PRU_KCODRIV] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO
    
    SET ANSI_PADDING ON
    GO
    
    /****** Object:  Index [<PRU_IDX_ME, sysname,>]    Script Date: 22/05/2019 07:26:45 ******/
    CREATE NONCLUSTERED INDEX [<PRU_IDX_ME, sysname,>] ON [dbo].[sma_PROD_USCITE]
    (
    	[PRU_KCODRIV] ASC
    )
    INCLUDE ( 	[PRU_KCODDLO],
    	[PRU_NUMUSC],
    	[PRU_KCODMIT],
    	[PRU_NCODTES],
    	[PRU_NCODVAR]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO
    
    SET ANSI_PADDING ON
    GO
    
    /****** Object:  Index [ind01]    Script Date: 22/05/2019 07:26:54 ******/
    CREATE NONCLUSTERED INDEX [ind01] ON [dbo].[sma_PROD_USCITE]
    (
    	[PRU_KCODDLO] ASC,
    	[PRU_KCODMIT] ASC,
    	[PRU_NCODTES] ASC,
    	[PRU_NCODVAR] ASC,
    	[PRU_KCODRIV] ASC
    )
    INCLUDE ( 	[PRU_KCODPRU],
    	[PRU_DATAUSC]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO
    
    
    SET ANSI_PADDING ON
    GO
    
    /****** Object:  Index [NonClusteredIndex-20190521-150329]    Script Date: 22/05/2019 07:27:01 ******/
    CREATE UNIQUE NONCLUSTERED INDEX [NonClusteredIndex-20190521-150329] ON [dbo].[sma_PROD_USCITE]
    (
    	[PRU_KCODDLO] ASC,
    	[PRU_KCODPRU] ASC,
    	[PRU_KCODRIV] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO
    
    SET ANSI_PADDING ON
    GO
    
    /****** Object:  Index [PK_sma_PROD_USCITE]    Script Date: 22/05/2019 07:27:11 ******/
    ALTER TABLE [dbo].[sma_PROD_USCITE] ADD  CONSTRAINT [PK_sma_PROD_USCITE] PRIMARY KEY CLUSTERED 
    (
    	[PRU_KCODDLO] ASC,
    	[PRU_KCODPRU] ASC,
    	[PRU_KCODRIV] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO
    
    
    SET ANSI_PADDING ON
    GO
    
    /****** Object:  Index [PRU_ABARCODE]    Script Date: 22/05/2019 07:27:19 ******/
    CREATE NONCLUSTERED INDEX [PRU_ABARCODE] ON [dbo].[sma_PROD_USCITE]
    (
    	[PRU_ABARCODE] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO
    
    
    

    thanks



    Emanuele

    Wednesday, May 22, 2019 5:28 AM
  • So the primary key is the columns you are updating by. I think at least the cursor solution should work fine with regards to locking and blocking other users - unless you have a transaction around the whole thing.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, May 22, 2019 9:13 PM
  • Hi, so with the MERGE is better/fast?

    DECLARE @json VARCHAR(max) 
    DECLARE @Sql  nVARCHAR(max)
    
    DECLARE @Cupd_KCODDLO VARCHAR(3)
    DECLARE @Cupd_KCODPRU BIGINT
    DECLARE @Cupd_ADESCRI varchar(50)
    DECLARE @Cupd_DATAUSC varchar(10)
    DECLARE @Cupd_NUMUSC int
    DECLARE @Cupd_QSCORTA int
    DECLARE @Cupd_ABARCODE varchar(18)
    DECLARE @Cupd_ASOTTO varchar(50)
    DECLARE @Cupd_IPRZCOP float
    DECLARE @Cupd_PERCDEF float
    DECLARE @Cupd_NPESO float
    DECLARE @Cupd_NCOMPRV float
    DECLARE @Cupd_KCODLVR varchar(1)
    DECLARE @Cupd_KCODEDT int
    DECLARE @Cupd_KCODPER int
    DECLARE @Cupd_KCODTPR int
    DECLARE @Cupd_KCODMIT int
    DECLARE @Cupd_NCODTES int
    DECLARE @Cupd_NCODVAR int
    DECLARE @Cupd_NCODDIF int
    DECLARE @Cupd_KCODRIV int
    DECLARE @Cupd_PERCSCB float
    DECLARE @Cupd_IPRZNET float
    
    DECLARE @INIZIO DATETIME
    DECLARE @contoINS int
    DECLARE @contoUPD int
    DECLARE @LOG bigint
    DECLARE @sLOG bigint
    
    		DECLARE @PruUpdate       TABLE (
    										upd_KCODDLO VARCHAR(3), upd_KCODPRU BIGINT, upd_ADESCRI varchar(50), upd_DATAUSC varchar(10),
    										upd_NUMUSC int, upd_QSCORTA int, upd_ABARCODE varchar(18), upd_ASOTTO varchar(50), upd_IPRZCOP float,
    										upd_PERCDEF float, upd_NPESO float, upd_NCOMPRV float, upd_KCODLVR varchar(1), upd_KCODEDT int,
    										upd_KCODPER int, upd_KCODTPR int, upd_KCODMIT int, upd_NCODTES int, upd_NCODVAR int, 
    										upd_NCODDIF int, upd_KCODRIV int, upd_PERCSCB float, upd_IPRZNET float
    										)
    DECLARE mycur1 CURSOR for
    select upd_KCODDLO , upd_KCODPRU , upd_ADESCRI , upd_DATAUSC ,
    		upd_NUMUSC , upd_QSCORTA , upd_ABARCODE , upd_ASOTTO , upd_IPRZCOP ,
    		upd_PERCDEF , upd_NPESO , upd_NCOMPRV , upd_KCODLVR , upd_KCODEDT ,
    		upd_KCODPER , upd_KCODTPR , upd_KCODMIT , upd_NCODTES , upd_NCODVAR , 
    		upd_NCODDIF , upd_KCODRIV , upd_PERCSCB , upd_IPRZNET 
    from @PruUpdate
    
    --SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    		SELECT @json = BulkColumn FROM OPENROWSET(BULK'D:\smartedi\20902PRU007592_20190521121645.json', SINGLE_BLOB) JSON;
    		SELECT @json
    set @contoINS = 0
    set @contoupd = 0
    set @sLOG = 0
    set @sLOG = (NEXT VALUE FOR sLOG)
    
    SET @INIZIO = GETDATE()
    
    BEGIN -- START UPDATE sma_PROD_USCITE --
    															INSERT INTO @PruUpdate 
    																SELECT 																			
    																		'209',
    																		JSON_Value (c.value, '$.PRU_KCODPRU') as PRU_KCODPRU, 
    																		JSON_Value (c.value, '$.PRU_ADESCRI') as PRU_ADESCRI, 
    																		JSON_Value (c.value, '$.PRU_DATAUSC') as PRU_DATAUSC, 
    																		JSON_Value (c.value, '$.PRU_NUMUSC')  as PRU_NUMUSC,
    																		JSON_Value (c.value, '$.PRU_QSCORTA')  as PRU_QSCORTA,
    																		JSON_Value (c.value, '$.PRU_ABARCODE')  as PRU_ABARCODE,
    																		JSON_Value (c.value, '$.PRU_ASOTTO')  as PRU_ASOTTO,
    																		JSON_Value (c.value, '$.PRU_IPRZCOP')  as PRU_IPRZCOP,
    																		JSON_Value (c.value, '$.PRU_PERCDEF')  as PRU_PERCDEF,
    																		JSON_Value (c.value, '$.PRU_NPESO')  as PRU_NPESO,
    																		JSON_Value (c.value, '$.PRU_NCOMPRV')  as PRU_NCOMPRV,
    																		JSON_Value (c.value, '$.PRU_KCODLVR')  as PRU_KCODLVR,
    																		JSON_Value (c.value, '$.PRU_KCODEDT')  as PRU_KCODEDT,
    																		JSON_Value (c.value, '$.PRU_KCODPER')  as PRU_KCODPER,
    																		JSON_Value (c.value, '$.PRU_KCODTPR')  as PRU_KCODTPR,
    																		JSON_Value (c.value, '$.PRU_KCODMIT')  as PRU_KCODMIT,
    																		JSON_Value (c.value, '$.PRU_NCODTES')  as PRU_NCODTES,
    																		JSON_Value (c.value, '$.PRU_NCODVAR')  as PRU_NCODVAR,
    																		JSON_Value (c.value, '$.PRU_NCODDIF')  as PRU_NCODDIF,
    																		JSON_Value (p.value, '$.PRU_KCODRIV') as PRU_KCODRIV, 
    																		JSON_Value (p.value, '$.PRU_NPERSCO') as PRU_NPERSCO,
    																		JSON_Value (p.value, '$.PRU_IPRZNET') as PRU_IPRZNET							
    																FROM OPENJSON (@json, '$.HEADER') as c
    																CROSS APPLY OPENJSON (c.value, '$.PRU_KCODRIV') as p
    														END;
    														set @LOG= @@ROWCOUNT
    														BEGIN
    															INSERT INTO sma_LOG_IMPORT
    																		(LOG_TIPOREC,LOG_BOS,LOG_NOMEFILE, LOG_NUMRECORD, LOG_NOTE,
    																			LOG_INIZIO,LOG_FINE, LOG_TEMPOEXEC, LOG_SEQUENCE)
    																	VALUES
    																		('PRU', '0', '', @LOG, 'INSERT TMP',
    																		@INIZIO, GetDate(), DATEDIFF(MILLISECOND, @INIZIO, GETDATE()), @sLOG)
    														END																									
    
    SET @INIZIO = GETDATE()
    
    begin
    		MERGE INTO sma_PROD_USCITE WITH (HOLDLOCK)
    
    		USING @PruUpdate 
    		ON (upd_KCODDLO = sma_PROD_USCITE.PRU_KCODDLO)
    		AND (upd_KCODRIV = sma_PROD_USCITE.PRU_KCODRIV)
    		AND (upd_KCODPRU = sma_PROD_USCITE.PRU_KCODPRU)
    
    		WHEN MATCHED THEN 
    			UPDATE SET 
    					PRU_ADESCRI  = upd_ADESCRI,  
    					PRU_DATAUSC  = CONVERT(date, upd_DATAUSC , 104) ,
    					PRU_NUMUSC   = upd_NUMUSC,  
    					PRU_QSCORTA  = upd_QSCORTA, 
    					PRU_ABARCODE = upd_ABARCODE, 
    					PRU_ASOTTO   = upd_ASOTTO, 
    					PRU_IPRZCOP  = upd_IPRZCOP,
    					PRU_PERCDEF  = upd_PERCDEF, 
    					PRU_NPESO    = upd_NPESO,   
    					PRU_NCOMPRV  = upd_NCOMPRV,  
    					PRU_KCODLVR  = upd_KCODLVR, 
    					PRU_KCODEDT  = upd_KCODEDT,
    					PRU_KCODPER  = upd_KCODPER, 
    					PRU_KCODTPR  = upd_KCODTPR, 
    					PRU_KCODMIT  = upd_KCODMIT,  
    					PRU_NCODTES  = upd_NCODTES, 
    					PRU_NCODVAR  = upd_NCODVAR,
    					PRU_NCODDIF  = upd_NCODDIF, 
    					PRU_PERCSCB  = upd_PERCSCB,  
    					PRU_IPRZNET  = upd_IPRZNET
    				--targetTable.ColumnA = sourceTable.ColumnA, 
    				--targetTable.ColumnB = sourceTable.ColumnB
    
    
    		WHEN NOT MATCHED THEN
    			INSERT  (
    					PRU_KCODDLO, PRU_KCODPRU, PRU_ADESCRI,  PRU_DATAUSC,
    					PRU_NUMUSC,  PRU_QSCORTA, PRU_ABARCODE, PRU_ASOTTO, PRU_IPRZCOP,
    					PRU_PERCDEF, PRU_NPESO,   PRU_NCOMPRV,  PRU_KCODLVR, PRU_KCODEDT,
    					PRU_KCODPER, PRU_KCODTPR, PRU_KCODMIT,  PRU_NCODTES, PRU_NCODVAR,
    					PRU_NCODDIF, PRU_KCODRIV, PRU_PERCSCB,  PRU_IPRZNET, PRU_ABARCODEPV 																																																
    					)VALUES
    					(
    					upd_KCODDLO, upd_KCODPRU, upd_ADESCRI,  upd_DATAUSC,
    					upd_NUMUSC,  upd_QSCORTA, upd_ABARCODE, upd_ASOTTO, upd_IPRZCOP,
    					upd_PERCDEF, upd_NPESO,   upd_NCOMPRV,  upd_KCODLVR, upd_KCODEDT,
    					upd_KCODPER, upd_KCODTPR, upd_KCODMIT,  upd_NCODTES, upd_NCODVAR,
    					upd_NCODDIF, upd_KCODRIV, upd_PERCSCB,  upd_IPRZNET, upd_ABARCODE 	
    					);
    end

    Thanks

    Emanuele

    Thursday, May 23, 2019 5:44 AM
  • Hi, so with the MERGE is better/fast?

    Is that a question or an observation?

    I don't knwo anything about your system, so I can't say exactly what will work for you. What I noted in my previous message is that the cursor solution should not cause any problem - unless it is wrapped in a transaction that I don't see, but which you have, for instance in your client code.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, May 23, 2019 9:00 PM