Update multiples columns data if record available then update other wise Insert

Transact-SQL
1

Hi Experts!
I have a question what is the fastest method to Update multiples columns data ,if record available then update other wise Insert columns data.
example please

2

Please provide destination table schema and version of SQL Server.

3

Records required to insert into or update from user database multiple tables columns to User database table( Destination table) ITEM_VOL. version Sqlserver 2012/Sqlserver2017
The Destination table is below

USE [UDB]
GO
--DROP TABLE ITEM_VOL
CREATE TABLE [dbo].[ITEM_VOL](
	[EVENT_GROUP_ID] [char](32) NULL,
	[ITEM_ID] [char](32) NULL,
	[EVENT_TYPE] [varchar](14) NULL,
	[PERIOD] [varchar](20) NULL,
	[ITEM_NAME] [varchar](20) NULL,
	[START_DATETIME] [datetime] NULL,
	[END_DATETIME] [datetime] NULL,
	[VAL1] [numeric](28, 12) NULL,
	[VAL2] [numeric](28, 12) NULL,

)
GO
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'12/30/2013',	'12/30/2013',1	)	
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'12/31/2013',	'12/31/2013',2	)	
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'1/1/2014',	'1/1/2014',	3	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'1/2/2014',	'1/2/2014',	4	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'1/3/2014',	'1/3/2014',	5	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'1/4/2014',	'1/4/2014',	6	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY',	'1/31/2014',	'1/31/2014',	7	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY1',	'5/6/2015',	'5/6/2015',	8	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY1',	'5/7/2015',	'5/7/2015',	9	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY1',	'5/8/2015',	'5/8/2015',	10	)
INSERT [dbo].[ITEM_VOL] ([EVENT_GROUP_ID], [ITEM_ID], [EVENT_TYPE], [PERIOD],[ITEM_NAME] , [START_DATETIME],[END_DATETIME], [VAL1])  VALUES('1','20607ddfa3a445ceb1fb03e76c8620e6','PROD_TEST',	'EVENT' ,	'FACILITY1',	'5/31/2015',	'5/31/2015',	11	)

Select * from ITEM_VOL
4

hi

if exist update other wise insert

these two things .. can be done in single statement .. called MERGE

please see below link ..
https://www.sqlshack.com/sql-server-merge-statement-overview-and-examples/

5 
;with SomeSource
as
(
		select '1' as EVENT_GROUP_ID,
	       '20607ddfa3a445ceb1fb03e76c8620e6' as ITEM_ID,
		   'PROD_TEST' as EVENT_TYPE,	
		   'EVENT' _PERIOD,	
		   'FACILITY UPDATED!' ITEM_NAME,	
		   '6/30/2015' START_DATETIME,	
		   '6/30/2015' END_DATETIME,	
		   11 as VAL1 union
	select '1' as EVENT_GROUP_ID,
	       '20607ddfa3a445ceb1fb03e76c8620e6' as ITEM_ID,
		   'WORLD_TEST' as EVENT_TYPE,	
		   'WW1' _PERIOD,	
		   'WAR INSERTED!' ITEM_NAME,	
		   '7/28/1914' START_DATETIME,	
		   '11/11/1918' END_DATETIME,	
		   999 as VAL1
)
MERGE ITEM_VOL t 
USING SomeSource s
   ON (s.VAL1 = t.VAL1)
WHEN MATCHED
THEN UPDATE SET 
        t.ITEM_NAME = s.ITEM_NAME,
		t.START_DATETIME = s.START_DATETIME,
        t.END_DATETIME = s.END_DATETIME
WHEN NOT MATCHED BY TARGET 
THEN INSERT (EVENT_GROUP_ID, 
             ITEM_ID, 
			 EVENT_TYPE, 
			 PERIOD, 
			 ITEM_NAME, 
			 START_DATETIME, 
			 END_DATETIME, 
			 VAL1)
     VALUES (s.EVENT_GROUP_ID, 
	         s.ITEM_ID, 
			 s.EVENT_TYPE, 
			 s._PERIOD, 
			 s.ITEM_NAME, 
			 s.START_DATETIME, 
			 s.END_DATETIME, 
			 s.VAL1);

select * From ITEM_VOL where VAL1  in (999, 11)
2 Likes
6

yosiasz Many thanks for your help! Thank you really! :slightly_smiling_face:

7

glad it helped. mark it as the answer