SQLTeam.com | Weblogs | Forums

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

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

Please provide destination table schema and version of SQL Server.

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

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/

;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)
1 Like

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

glad it helped. mark it as the answer