1

We are planning to convert an application from Sybase SQL Anywhere to SQL Server.

At SA we use a lot of "on existing update", that checks by primary key, if line exists. If true, is update (in same way if I was executing update clause). If not, it inserts.

Does SQL Server supports something like this?

Zote
  • 5,343
  • 5
  • 41
  • 43

3 Answers3

1

AFAIK there is no support for this in one command. And the best way I found was follow Jeremiah Clark's tip: try to update, then check for the number of affected records. If it was zero, then I insert:

UPDATE Table1 SET (...) WHERE Column1='SomeValue'
IF @@ROWCOUNT=0
    INSERT INTO Table1 VALUES (...)
Erick Sasse
  • 2,779
  • 3
  • 24
  • 30
  • I'm looking for a way to avoid tests. But if it does not have, probably I'll use this way. – Zote Aug 04 '11 at 15:49
1

If you will use SQL Server 2008 and later, you can use MERGE command. You can find description here:

http://technet.microsoft.com/en-us/library/bb510625(SQL.100).aspx

If you use older SQL, Jeremiah Clark's solution suggested by Erick Sasse will be ok.

Dalex
  • 3,585
  • 19
  • 25
0

SQL Server has all the features of an high-end RDBMS, Stored Procedures, Triggers, UDF and so on... as you probably know MS SQL Server was branched out exactly from Sybase so they do have common roots.

I am not sure I get your question completely, of course you can have a SQL Statement which contains some logic and does INSERT or UPDATE depending on some conditions or if a record was already found, TSQL is the SQL dialect of SQL Server and supports this and much more.

If you really have a specific question or doubt on a statement please show us your statement as you have it right now in SA.

Davide Piras
  • 43,984
  • 10
  • 98
  • 147
  • insert into TableA (id, value1, value2) on existing update values (1, 'ABC', 123); – Zote Aug 04 '11 at 17:06
  • if TableA have a line with id (pk) equals 1, it will update. If not, it will insert. – Zote Aug 04 '11 at 17:06
  • FYI SQL Server was branched from Sybase __ASE__. Sybase SQL Anywhere is a completely different RDBMS that has no code in common with ASE. – Graeme Perrow Aug 04 '11 at 17:19