Sql-server – How to accomplish such insert-or-update strategy

insertsql serversql-server-2012stored-proceduresupdate

Programming stored procedures isn't my strongest point when it comes to programming, but I'm trying to get better.

I've stumbled upon a business requirement that's kind of hard for me and I was wondering if anyone had already dealt with it in their career.

There's this data in a staging table that needs to be loaded in a production table with insert for the new data and insert-update(backup like) for the old data.

The sample of data in production table looks like this:

AssetID	BeginDate	EndDate	Field1	Field2	FieldN
1	(TS)	(TS)	0.2351	1.3510	4.1234

Meanwhile in staging:

AssetID	Field1	Field2	FieldN
1	0.2351	1.3510	4.1234

In prod the PK is AssetID and BeginDate meanwhile in staging is only AssetID (because the data is flushed every time).

The requirement is to write a stored procedure that when there are new assets in the staging area, they are inserted as new in prod(with BeginDate as CURRENT_TIMESTAMP and EndDate as NULL), meanwhile if there are data(in staging) that are different on the Field1,Field2 or FieldN fields from the production one, then a new record is written with the same AssetID but BeginDate as CURRENT_TIMESTAMP and EndDate as NULL, and the old record with the same combination (BeginDate memorized and EndDate is NULL) being updated with an EndDate as CURRENT_TIMESTAMP

Any of you have dealt with a similar task/requirement? If yes, could you share your solution please?

Best Answer

You have to try to finalize any existent opened record for the given AssetID before insertion of the new record.

BEGIN
  DECLARE ts TIMESTAMP = NOW();
  DECLARE f1, f2, fN FLOAT; 

  SELECT Field1, Field2, FieldN
    FROM prod_table
   WHERE AssetID = _givenID
     AND EndDate = NULL
    INTO f1, f2, fN
  ;

  IF ( f1 <> _field1 OR 
       f2 <> _field2 OR 
       fN <> _fieldN )
    THEN 
      UPDATE prod_table    -- Here we suppose there is zero or one opened record
         SET EndDate = ts  -- If there is no records for _givenID, update do nothing
       WHERE AssetID = _givenID
         AND EndDate IS NULL  
      ;

      INSERT prod_table (AssetID, BeginDate, EndDate, Field1, Field2, FieldN)
      VALUES ( _givenID, ts, NULL, _field1, _field2, _fieldN )
      ;
  END IF;
END

Related Solutions

SQL Server – How to Determine if Insert or Update in SQL Server 2008

Since it's only UPDATE,INSERT you can say:

IF EXISTS (SELECT 1 FROM deleted)
  -- update
ELSE
  -- insert

You have a bigger problem, though. There is no such thing as new.Id, and an insert or update can affect multiple rows (in some platforms, triggers fire per row; in SQL Server, they fire per operation). So you need to either:

Use a loop to call the stored procedure for all of the RecordId values in inserted.
Stop using a stored procedure for this and perform whatever logic it does inside the trigger, as a set-based operation.

SQL Server – Update and Insert Statement Taking Forever

Thanks for updating that you are using Microsoft SQL Server, which does support Full Text Indexing. (As do many other versions of SQL servers.)

First of all your query is using a WHERE (Word LIKE @partialWord) which means that if there is a prefix wildcard (e.g. '%tion') the whole table has to be scanned and the contents of the Word column likewise scanned.

That is the base cost of this query.

Therefore, you need to investigate some other method of finding the partial words. For example, full text indexing (if available to you) may give you other options to use when finding %partial% words.

Create a full text index on the Predictions table for the Word column. This can be done through SSMS or by a script. This will populate the keywords table in the FTI catalog tables. Example from: http://msdn.microsoft.com/en-us/library/ms187317.aspx

CREATE UNIQUE INDEX pk_PredictionsID ON dbo.Predictions(PredictionsID);
CREATE FULLTEXT CATALOG ft AS DEFAULT;
CREATE FULLTEXT INDEX ON dbo.Predictions(Word) 
   KEY INDEX pk_PredictionsID 
   WITH STOPLIST = OFF;

Then here is a sample script that would help you to then find all the rows with wildcards.

DECLARE @SearchText NVARCHAR(2000)
SET @SearchText = ''

DECLARE @Pattern NVARCHAR(100)
SET @Pattern = '%colat%'

SELECT @SearchText = @SearchText + display_term + ' OR ' 
FROM sys.dm_fts_index_keywords ( DB_ID('DBName'),
    object_id('Predictions') )
   WHERE display_term like @Pattern

SELECT @SearchText = LEFT (@SearchText, LEN(@SearchText) - 3)
-- Contents "chocolate OR percolator" in my test case

SELECT * FROM Predictions
WHERE CONTAINS(*, @SearchText)

By building a full text query string that includes all strings containing the pattern joined by the OR operator, it allows Full Text Search to then directly access the rows that contain one (or more) of the words. This should be a lot faster than scanning the table.

Best Answer

Related Solutions

SQL Server – How to Determine if Insert or Update in SQL Server 2008

SQL Server – Update and Insert Statement Taking Forever

Related Question