SQL Select: Refresh, if exists, Paste, if not - Comparing date of part?

Question

I need to update a database record with the following fields

[ID] int (AutoIncr. PK) [ScorerID] int [Score] int [DateCreated] smalldatetime 

If there is a record for today's date (only the date stamp should be checked, not the time) and this scorer, I would like to update the score value for this guy on this day as well. If scorer has no record for today, I would like to create a new one.

I get gray hair trying to figure out how to put this in one (is this possible?) Sql expression. By the way, I use the MSSQl database and the ExecuteNonQuery() method for the query.

Answer 1

 IF EXISTS (SELECT NULL FROM MyTable WHERE ScorerID = @Blah AND CONVERT(VARCHAR, DateCreated, 101) = CONVERT(VARCHAR, GETDATE(), 101)) UPDATE MyTable SET blah blah blah ELSE INSERT INTO MyTable blah blah blah 

Answer 2

The rest of the guys reviewed compatible with Microsoft (and earlier) compatible T-SQL / apprroaches. I just wanted to add that if you were lucky enough to work with SQL Server 2008, you can use the new Merge statement (sometimes called Upsert).

I was not able to find a blog post or article that explains this further, but I found this rather (1) useful post . The official MSDN (2) entry is here.

(1) [ http://www.sqlservercurry.com/2008/05/sql-server-2008-merge-statement.html]
(2) [ http://msdn.microsoft.com/en-us/library/bb510625.aspx]

Answer 3

 CREATE PROCEDURE InsertOrUpdateScorer(@ScorerID INT, @Score INT) AS BEGIN IF EXISTS ( SELECT 1 FROM Scorer WHERE ScorerID = @ScorerID AND DATEDIFF(dd, GETDATE(), DateCreated) = 0 ) BEGIN UPDATE Scorer SET Score = @Score WHERE ScorerID = @ScorerID RETURN @ScorerID END ELSE BEGIN INSERT Scorer (ScorerID, Score, DateCreated) VALUES (@ScorerID, @Score, GETDATE()) RETURN SCOPE_IDENTITY() END END 

Use the return value of the procedure to capture the new ScorerId.

 SqlCommand UpdateScorer = New SqlCommand("InsertOrUpdateScorer", DbConn); UpdateScorer.CommandType = CommandType.StoredProcedure; SqlParameter RetValue = UpdateScorer.Parameters.Add("RetValue", SqlDbType.Int); RetValue.Direction = ParameterDirection.ReturnValue; SqlParameter Score = UpdateScorer.Parameters.Add("@Score", SqlDbType.Int); Score.Direction = ParameterDirection.Input; SqlParameter ScorerId = UpdateScorer.Parameters.Add("@ScorerID", SqlDbType.Int); ScorerId.Direction = ParameterDirection.Input; Score.Value = 15; // whatever ScorerId.Value = 15; // whatever UpdateScorer.ExecuteNonQuery(); Console.WriteLine(RetValue.Value); 

Answer 4

In the case when you want to update or insert all the values ​​at the same time, not only for one record, I used this fragment

Run update script

  UPDATE Table1 
 SET OPIS = T1.OPIS
 FROM
     Table1 AS T
 INNER JOIN
     Table2 AS T1
     ON
         T.col = T1.col;

Then paste the script

  INSERT INTO Table1 
 SELECT * FROM 
     (
         SELECT T1. * Table2 AS T1
         LEFT JOIN Table1 AS T2 ON (T2.col = T1.col)
         WHERE T2.col IS NULL
     ) AS T;

Hope someone found this helpful.

The equivalent of this in MySql (in some cases) looks something like this:

  INSERT INTO table (a, b, c) VALUES (1,2,3)
   ON DUPLICATE KEY UPDATE c = c + 1;

Someone may find that this is due to an article in SQL Server INSERT or UPDATE Solutions

Updated version using MERGE (Transact-SQL) :

 DECLARE @USER_ID AS INT=76; DECLARE @TYPE AS NVARCHAR(MAX)='set.global'; DECLARE @FKEY AS NVARCHAR(MAX)='21'; DECLARE @DATA AS NVARCHAR(MAX)='test'; begin tran MERGE UserData USING (SELECT @USER_ID, @TYPE, @FKEY, @DATA) AS Source([UserId], [Type], [FKey], [Data]) ON (UserData.[UserId] = Source.[UserId] AND UserData.[Type] = Source.[Type] AND (UserData.[FKey] = Source.[FKey] OR (Source.[FKey] IS NULL AND UserData.[FKey] IS NULL))) WHEN MATCHED THEN UPDATE SET [Data] = Source.[Data] WHEN NOT MATCHED BY TARGET THEN INSERT ([UserId] ,[Type] ,[FKey] ,[Data]) VALUES ( Source.[UserId] ,Source.[Type] ,Source.[FKey] ,Source.[Data]); commit tran