SQL Server If Else and Insert /Update

Question

Why does SQL Server not support to insert/update? It only supports select. Is there any alternative for In Duplicate Key of MySQL in SQL Server?

Please help..!

IF(SELECT accountNo 
   FROM MeterReading 
   WHERE accountNo = 1111) is NULL 
    (INSERT INTO dbo.MeterReading  
     VALUES (1, 1, 1, 1)); 
ELSE 
    (UPDATE dbo.MeterReading 
     SET currentReading = 1223 
     WHERE accountNo = 1111);

Why did you tag this question with java instead of sql-server? I don't see anything here that relates to java...? — azurefrog, May 21 '18 at 16:21
Possible duplicate of [Solutions for INSERT OR UPDATE on SQL Server](https://stackoverflow.com/questions/108403/solutions-for-insert-or-update-on-sql-server) — DavidG, May 21 '18 at 16:38
in modern sqlserver versions it's called a [MERGE](https://learn.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql?view=sql-server-2017#a-using-merge-to-perform-insert-and-update-operations-on-a-table-in-a-single-statement) — fnostro, May 21 '18 at 17:35
Does not make sense to not insert accountNo and currentReading. — paparazzo, May 21 '18 at 17:45

score 0 · Answer 1 · answered May 21 '18 at 16:30

0

You could go for an UPSERT:

UPDATE MeterReading SET currentReading=1223 WHERE accountNo=1111

IF @@ROWCOUNT = 0
   INSERT INTO MeterReading VALUES (1, 1, 1, 1)

answered May 21 '18 at 16:30

Daniel Almeida

372
1
14

score 0 · Accepted Answer · answered May 21 '18 at 17:31

Try this syntax:

IF NOT EXISTS(SELECT accountNo 
               FROM MeterReading 
               WHERE accountNo = 1111) 
  BEGIN 
    INSERT INTO dbo.MeterReading  
    VALUES (1, 1, 1, 1) 
  END 
ELSE 
  BEGIN 
  UPDATE dbo.MeterReading 
  SET currentReading = 1223 
  WHERE accountNo = 1111
  END

score 0 · Answer 3 · answered May 21 '18 at 17:38

0

You could try Merge as well

MERGE MeterReading AS target  
    USING (SELECT 1111, 1223) AS source (accountNo, currentReading)  
    ON (target.accountNo = source.accountNo )  
WHEN MATCHED THEN   
    UPDATE SET currentReading  = source.currentReading   
WHEN NOT MATCHED THEN  
    INSERT (col1,col2,col3,col4)  
    VALUES (1, 1, 1, 1);

answered May 21 '18 at 17:38

JamieD77

13,796
1
17
27

there is a error with 2nd "when" and it requires ; Failed to execute query. Error: Incorrect syntax near the keyword 'WHEN'. – Dhanushka Sandaruwan May 21 '18 at 19:03

SQL Server If Else and Insert /Update

3 Answers3