Question about inserting/updating rows with SQL Server (ASP.NET MVC)

Question

I have a very big table with a lot of rows, every row has stats for every user for certain days. And obviously I don't have any stats for future. So to update the stats I use

UPDATE Stats SET Visits=@val WHERE ... a lot of conditions ... AND Date=@Today

But what if the row doesn't exist? I'd have to use

INSERT INTO Stats (...) VALUES (Visits=@val, ..., Date=@Today)

How can I check if the row exists or not? Is there any way different from doing the COUNT(*)?

If I fill the table with empty cells, it'd take hundreds of thousands of rows taking megabytes and storing no data.

Answer 1

I would try an UPDATE, then if @@ROWCOUNT = 0 try an INSERT

UPDATE Stats SET Visits=@val WHERE ... a lot of conditions ... AND Date=@Today
IF @@ROWCOUNT = 0
   INSERT INTO Stats (...) VALUES (Visits=@val, ..., Date=@Today)

Or try an INSERT inside a TRY/CATCH and then UPDATE if it fails.

I wouldn't test first

Answer 2

What I tend to do in those cases is just perform the following (pseudo-code since I'm a DB2 man myself):

try:
    INSERT
catch already-exists:
    try:
        UPDATE
    catch anything:
        generate error

You can do it in the opposite order if you're more likely to have rows than not:

try:
    UPDATE
catch not-there:
    try:
        INSERT
    catch anything:
        generate error

You never test first in a DBMS since the database can change underneath you between the test and the execution (the "better to ask forgiveness than seek permission" principle).

Answer 3

Unfortunately SQL Server doesn't have any kind of "INSERT OR UPDATE" command (that I know of), like some DB engines. That means that you do have to check for the row first or catch the error as other answers recommend. Hopefully your row has an artificial primary key, so what you could is first query for that key using all your WHERE conditions and then, if a row is returned do an UPDATE ... WHERE key = ..., otherwise do an INSERT. That way you're not running the entire query twice.

Answer 4

If you're using SQL Server 2008 and up, you should also investigate the MERGE statement introduced in the 2008 version.

• MERGE on Technet
• Using SQL Server 2008's MERGE statement
• SQL SERVER – 2008 – Introduction to Merge Statement – One Statement for INSERT, UPDATE, DELETE
• SQL Server 2008 MERGE statement