-1

I need to run a query at specific time an save it in other table, that part I've already done it, but later I need to run the same query but need to exclude the data that it was generated in the first run of the query. It is a process that has to be done three times a day. Any ideas? Thanks.

Skatalitico
  • 37
  • 7
  • You can make a SQL Server Agent job to have it run a stored procedure or whatever code you want 3x a day. – tshoemake Dec 28 '15 at 16:30
  • Can you just add a `DailyFlag BIT` column that is set to 0 for all rows except those that you insert in your first query? If those rows you freshly insert have this flag set to `1`, you can easily just delete those rows later on in the day – marc_s Dec 28 '15 at 16:34
  • Possible duplicate of [SQL Insert into table only if record doesn't exist](http://stackoverflow.com/questions/16460397/sql-insert-into-table-only-if-record-doesnt-exist) – Tab Alleman Dec 28 '15 at 16:54
  • Give the tables names and way more detail. This is going to get closed. – paparazzo Dec 28 '15 at 17:53
  • @TabAlleman How are you reading if doesn't exists as a duplicate to "but removing" – paparazzo Dec 28 '15 at 17:55
  • @Frisbee, I interpret OP's question as he needs to run a query and insert the results into a table. "But removing the data that is saved in the table" means he needs to run his query and only insert the rows that don't already exist in the table. It's a very common need. – Tab Alleman Dec 28 '15 at 18:10
  • "Removing" would be a very uncommon way to express that common need. I would think remove from the source after inserting in the target to be a much more likely the intent here. – paparazzo Dec 28 '15 at 18:20

1 Answers1

0

The question is a little confusing, but I think you want to backup several times a day the records that were inserted in the source table after the last backup.

Let's assume that your structure is the following:

**DataTable**
Id (IDENTITY)
Value

**BackupTable**
Id
SourceId             -- identifies the source. Can be used when merging data from multiple sources
RefId                -- identifier in source's scope (in our case DataTable.Id)
Value                -- value copied

In our case, the job should look for those values that are not yet inserted:

DECLARE @SourceId INT = 1            -- this is a constant for DataTable as source
DECLARE @maxIdForSource INT = (SELECT MAX(RefId) FROM BackupTable WHERE SourceId = @SourceId)

INSERT INTO BackupTable
(SourceId, RefId, Value)
SELECT @SourceId, Id, Value
FROM DataTable 
WHERE Id > @maxIdForSource

If you are not merging data from multiple sources, you do not define SourceId at all.

Alexei - check Codidact
  • 22,016
  • 16
  • 145
  • 164