September 14, 2012 at 12:26 pm
Hi all -
I'm trying to run an update query based on the last time the update query was run.
Ex:
I run the query at 5pm (via a once every 24 hour job). This query updates table1 and writes a timestamp in table2, denoting when the update statement started. Let's say I want to run (manually) it again at 7pm, I want to update only the records that were added since the last run at 5pm.
Any thoughts?
Dan
September 14, 2012 at 12:43 pm
robert.baird 2778 (9/14/2012)
Hi all -I'm trying to run an update query based on the last time the update query was run.
Ex:
I run the query at 5pm (via a once every 24 hour job). This query updates table1 and writes a timestamp in table2, denoting when the update statement started. Let's say I want to run (manually) it again at 7pm, I want to update only the records that were added since the last run at 5pm.
Any thoughts?
Dan
Do you have a column in table1 that is updated to the current time when the update is fired?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 14, 2012 at 12:48 pm
No.. that is stored in table2
table1:
Name
Addy
Ph.
table2
RunDateTime
The package writes a new timestamp in table2 everytime its fired off, showing the begin time of the package (the update to table1).
September 14, 2012 at 12:50 pm
robert.baird 2778 (9/14/2012)
No.. that is stored in table2table1:
Name
Addy
Ph.
table2
RunDateTime
The package writes a new timestamp in table2 everytime its fired off, showing the begin time of the package (the update to table1).
Can you post ddl (create table scripts), sample data (insert statements) and desired output based on your sample data? Take a look at the first link in my signature about best practices when posting questions.
So I take it that in table2 you only record the time but not the primary key that was updated. If you have know way of determining which rows were updated you are probably stuck. Once you post up some ddl we can have a look.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 14, 2012 at 1:06 pm
Ok...
So these are the first two tasks in SSIS.
First runs the insert to the 'META' table.
Second task inserts the records into the 'Primary_Import' table.
The way I wanted this to work is that it looks at the date in the META table and imports only the records after that timestamp..
META Table:
/****** Object: Table [dbo].[Primary_Import_Meta] Script Date: 09/14/2012 13:54:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Primary_Import_Meta](
[RunDate] [datetime] NULL,
[StatusFlag] [int] NULL,
[MetaID] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]
GO
Primary table:
/****** Object: Table [dbo].[Primary_Surround_Import] Script Date: 09/14/2012 13:54:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Primary_Surround_Import](
[TRANSMITID] [numeric](30, 0) NULL,
[CDATE] [datetime] NULL,
[TRANSFER_DATE] [datetime] NULL,
[SAMPLEID] [varchar](50) NULL,
[TRANSDATAID] [numeric](30, 0) NULL,
[CUTDATE] [datetime] NULL,
[RDATE] [datetime] NULL,
[CLIENTID] [numeric](30, 0) NULL,
[PANELID] [numeric](30, 0) NULL,
[CLEAR_STATUS] [varchar](10) NULL,
[HBS] [varchar](5) NULL,
[HIV] [varchar](5) NULL,
[HCV] [varchar](5) NULL,
[HBC] [varchar](5) NULL,
[HT1] [varchar](5) NULL,
[FT1] [varchar](5) NULL,
[FT2] [varchar](5) NULL,
[FT3] [varchar](5) NULL,
[FT4] [varchar](5) NULL,
[FT5] [varchar](5) NULL,
[FT6] [varchar](5) NULL,
[FT7] [varchar](5) NULL,
[FT8] [varchar](5) NULL,
[FT9] [varchar](5) NULL,
[FT10] [varchar](5) NULL,
[CMV] [varchar](5) NULL,
[ALT] [varchar](5) NULL,
[ALTIU] [varchar](5) NULL,
[ABY] [varchar](5) NULL,
[RPR] [varchar](5) NULL,
[ABO] [varchar](5) NULL,
[PHENO] [varchar](5) NULL,
[KELL] [varchar](5) NULL,
[CHL] [varchar](5) NULL,
[STATUS_MDATE] [datetime] NULL,
[DRAW_DATE] [datetime] NULL,
[STATUS] [varchar](5) NULL,
[IS_PROCESSING] [int] NULL,
[SYSTEM_SOURCE] [varchar](10) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[Primary_Import] ADD CONSTRAINT [DF_Primary_Import_IS_PROCESSING] DEFAULT ((1)) FOR [IS_PROCESSING]
GO
Once the package starts, the table1 date is written, and all the imports happen in table2. If i run it again, it should look at the timestamp in table, and only get the records that have been added (in the source system) that have a date AFTER that..
Cheers!
September 14, 2012 at 1:13 pm
Something is missing here because you reference Primary_Import in your explanation and in your code but the table is not created.
The meat of your issue is found here:
The way I wanted this to work is that it looks at the date in the META table and imports only the records after that timestamp..
All you need to do is alter your insert statement to something like this.
insert Primary_Import (columns)
select (columns)
from SourceTable
where SomeDateThatIndicatesYourLogic > (select MAX(RunDate) from Primary_Import_Meta)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply