Member Avatar for smeboyds

I have a database that continually receives information from outside sources. I then use SQL code to take this data and move to different databases based on the information received. As it stands right now, if I receive a '1', '2', '3', from an outside source, we manually move it. Is it possible to move the data for a period of time if a certain data set enters the database? i.e. I receive a '2' from the outside source and for the next 5 minutes, all data from that source is automatically transferred. After the 5 minutes is up, the data needs to be moved manually again until a '2' is received again.

  • 3 Contributors
  • 2 Replies
  • 124 Views
  • 12 Hours Discussion Span
  • Latest Post Latest Post by Cruize_Invades

Dani AI

Generated

This can be done reliably without blocking inserts by using a per-source “active window” plus an asynchronous mover. This note expands on points from and ties into ideas from and while giving a concrete pattern you can implement and tune.

Suggested pattern (high level):

  • Stage every incoming row in a small staging table (ID, SourceID, Payload, ArrivedAt UTC, Processed bit).
  • Maintain an ActiveWindow record per source (SourceID, StartTime, EndTime). When a message with value '2' arrives, upsert the ActiveWindow for that SourceID with EndTime = DATEADD(minute,5,GETUTCDATE()) so repeated '2's extend the window.
  • Run an asynchronous mover (SQL Agent job, or Service Broker activation) that moves staging rows where Processed = 0 and ArrivedAt is inside the current active window. Mark rows Processed inside the same transaction to ensure idempotency.

Minimal T-SQL examples (adapt to your schema):

-- upsert active window when '2' arrives
IF EXISTS (SELECT 1 FROM ActiveWindow WHERE SourceID = @SourceID)
  UPDATE ActiveWindow
  SET StartTime = GETUTCDATE(), EndTime = DATEADD(minute,5,GETUTCDATE())
  WHERE SourceID = @SourceID;
ELSE
  INSERT INTO ActiveWindow (SourceID, StartTime, EndTime)
  VALUES (@SourceID, GETUTCDATE(), DATEADD(minute,5,GETUTCDATE()));
-- mover job (run often, or triggered via SB)
DECLARE @Moved TABLE (ID INT);

BEGIN TRAN;
INSERT INTO Destination (...) 
OUTPUT s.ID INTO @Moved
SELECT ... FROM Staging s
JOIN ActiveWindow w ON s.SourceID = w.SourceID
WHERE s.Processed = 0
  AND s.ArrivedAt >= w.StartTime
  AND s.ArrivedAt < w.EndTime;

UPDATE s SET Processed = 1
FROM Staging s JOIN @Moved m ON s.ID = m.ID;
COMMIT TRAN;

Practical notes: avoid doing heavy work inside insert triggers; use a trigger only to enqueue or set flags. Use GETUTCDATE() for consistent timing, index (SourceID, ArrivedAt, Processed), make the mover idempotent (unique keys or checks), and test concurrency. For near-real-time scaling consider Service Broker; for simpler setups a short-interval SQL Agent job is fine. See the Service Broker and SQL Agent docs for implementation details: Service Broker documentation and SQL Server Agent.

Recommended Answers

All 2 Replies

Member Avatar for adam_k

As a general rule, SQL is build to handle group of records (matching criteria) simultaneously. Your description looks like you are thinking linear processing, which will not work that well with SQL and it will definately not allow you to use your server 100%.
Anyway, you'll need to provide more info on what you are building, perhaps other have done something similar and can share practises or we can get a better idea of your need.

Assuming a lot, if this is not a time critical application, you could search for the 1st unhandled '2' and get all messages followed by that in a 5 min window. Unfortunately you'd have to run this every x mins and would read messages already in the db - it won't wait for new messages to act on.

Member Avatar for Cruize_Invades

You can schedule it. MS SQL has scheduling, so you can process your task here let say every 5 mins.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.