Multi SQL statements executed via ADO.NET command object

Question

I need to update a record in a table but it is possible that the record does not exist, so I would need to insert the record.

Below is a SQL statement that accomplished my goal by trying to update the record first and if it doesn't exist it performs an insert. What I am wondering if it can be executed directly via ADO.NET command object or does it need to go into a stored procedure.

UPDATE MyTable SET ReservationDate = @ReservationDate WHERE CustomerNumber = XXXX;
IF @@ROWCOUNT=0
  INSERT INTO MyTable (CustomerNumber , ReservationDate) 
  VALUES (@CustomerNumber , @ReservationDate)

If I could execute it via the command object without a stored procedure it would mean one less dependency for deployment time (i.e. deploying the stored procedure).

Steve · Accepted Answer · 2014-11-08 23:48:37Z

2

The MERGE command in T-SQL works for just this scenario

string cmdText = @"MERGE MyTable T
                   USING (SELECT @CustomerNumber As CustomerNumber) as S 
                   ON T.CustomerNumber = S.CustomerNumber
                   WHEN MATCHED UPDATE SET ReservationDate = @ReservationDate 
                   WHEN NOT MATCHED INSERT INTO (CustomerNumber , ReservationDate)  
                                    VALUES (@CustomerNumber , @ReservationDate)";

It is just one string of text wrapped for readability in more lines thanks to the verbatim character @

With MERGE you start defining your TARGET table (T), then you build a pseudotable called SOURCE (S) with the parameter that contains the value for the primary key field in TARGET. Now the two tables are JOINED ON the field CustomerNumber. The product of this join could be MATCHED or NOT MATCHED depending of the previous presence of the record in the TARGET table. The remainder of the query is probably self explanatory, just notice that in the two actions (UPDATE and INSERT) there is no need to repeat the MyTable name (it is the TARGET)

By the way, yes you could pass multiple commands to the SqlCommand separated by a semicolon

edited Nov 8, 2014 at 23:48

answered Nov 8, 2014 at 20:53

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

webworm Over a year ago

Awesome answer and crystal clear example! I had never known of the Merge command. Thank you!

webworm Over a year ago

Could this also work if more then field was used in determining which records were updated/inserted? For example ... what if it was not just CustomerNumber but also VehicleNumber that was used when updating/inserting a reservation? Like if a Customer had multiple vehicles.

Steve Over a year ago

Why not? Just prepare the appropriate parameters, use them in building your SOURCE table and adjust the JOIN to match/nomatch the primary key of target. UPDATE or INSERT follows

Collectives™ on Stack Overflow

Multi SQL statements executed via ADO.NET command object

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related