November 4, 2020 at 3:24 pm
This may have an easy resolution but I'm having difficulty finding it. I'm creating a package that needs to take records that are new or updated since the last time the job run. I have 2 DateTime columns Insert_date and Last_Updated. I need records for newly inserted records and modified/last_updated records. How do I accomplish that with a SQL script and how do I construct that script or conditional split. I know I could use the MAX function but seem to only get back the latest Last_Updated records not the latest inserted records. Any help would be appreciated.
November 4, 2020 at 3:30 pm
WHERE (Insert_date > @LastJobRun OR Last_Updated > @LastJobRun)
November 4, 2020 at 4:09 pm
Thanks, Jonathon but I'm not getting any results. I'm new to SQL and SSIS learning as I go this is the query I'm using in the OL DB SOURCE for the package:
DECLARE @LastJobRun datetime;
SELECT [ID]
,[SETID]
,[ACCOUNT]
,[ACCT_DESCR]
,[ACCT_DESCRSHORT]
,[ACCOUNT_TYPE]
,[ACCT_TYPE_DESCR]
,[BALANCE_FWD_SW]
,[BUDGETARY_ONLY]
,[INSERT_DATE]
,[EFF_DATE]
,[ACCT_STATUS]
,[LAST_UPDATED]
FROM [DCRPT].[dbo].[AMOUNT]WHERE (INSERT_DATE > @LastJobRun OR LAST_UPDATED > @LastJobRun)
November 4, 2020 at 4:23 pm
You need to set the value of the @LastJobRun parameter before executing that query.
Do you have this stored somewhere?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 4, 2020 at 4:37 pm
I do not. What would be the best way to go about that as a variable?
November 4, 2020 at 4:42 pm
I do not. What would be the best way to go about that as a variable?
When the job runs, does it write to a log table? (Containing, for example, RunNumber, StartedAt, CompletedAt, IsSuccess)?
If you have a table like that, you can query it to get the most recent RunDate
SELECT TOP (1) StartedAt
FROM ETLLog
WHERE IsSuccess = 1
ORDER BY RunNumber DESC
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 4, 2020 at 4:44 pm
Currently, I don't have the value stored anywhere. How would I write it as a subquery?
November 4, 2020 at 4:50 pm
Phil I don't believe the job writes to a log table. This is a table that gets updated daily I want to get the updated records and the latest inserted records from this table extracted into a CVS file just newly inserted and changed records nothing else.
November 4, 2020 at 5:03 pm
Ok, I got confused but once the package is created we will deploy it and schedule it to run in a SQL job.
November 4, 2020 at 5:26 pm
dface_21 wrote:Currently, I don't have the value stored anywhere.
How do you know when the job last ran?
+1, if you don't know when it last ran, this is close to impossible.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 4, 2020 at 5:28 pm
Could you
SELECT MAX(Last_Updated)
from your target table and use that instead?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 4, 2020 at 5:49 pm
If I use the query below I get the LAST_UPDATED fields but no newly INSERT_DATE records which I should get atleast one record.
SELECT t.[ID]
,t.[SETID]
,t.[ACCOUNT]
,t.[ACCT_DESCR]
,t.[ACCT_DESCRSHORT]
,t.[ACCOUNT_TYPE]
,t.[ACCT_TYPE_DESCR]
,t.[BALANCE_FWD_SW]
,t.[BUDGETARY_ONLY]
,t.[INSERT_DATE]
,t.[ACCT_STATUS]
,t.[EFF_DATE]
,t.[LAST_UPDATED]
FROM [DCRPT].[dbo].[AMOUNT] t
inner join (
select ID, MAX(INSERT_DATE) AS INSERT_DATE, MAX(LAST_UPDATED) AS LAST_UPDATED
FROM [dbo].[AMOUNT]
Group By [ID]
) tm on t.ID = tm.ID and t.INSERT_DATE = tm.INSERT_DATE and t.LAST_UPDATED = tm.LAST_UPDATED
November 4, 2020 at 7:01 pm
Try something like this (I wasn't sure of the names of your source and target tables, so I'll leave that to you):
DECLARE @MaxInsertDate DATETIME
,@MaxLastUpdated DATETIME;
SELECT @MaxInsertDate = MAX(Insert_Date)
,@MaxLastUpdated = MAX(Last_Updated)
FROM TargetTable;
SELECT t.ID
,t.SETID
,t.ACCOUNT
,t.ACCT_DESCR
,t.ACCT_DESCRSHORT
,t.ACCOUNT_TYPE
,t.ACCT_TYPE_DESCR
,t.BALANCE_FWD_SW
,t.BUDGETARY_ONLY
,t.INSERT_DATE
,t.ACCT_STATUS
,t.EFF_DATE
,t.LAST_UPDATED
FROM SourceTable t
WHERE t.Insert_Date >= @MaxInsertDate
OR t.Last_Updated >= @MaxLastUpdated;
This will bring in some rows which have already been loaded, so you'll need to handle that, but should not miss anything.
If rows can be deleted from the source table, you will need to handle that separately. This method finds only new and modified rows.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply