June 19, 2014 at 9:20 am
Need some help for incremntal load on below data
CREATE TABLE [dbo].[Source](
[ID] [char](15) NOT NULL,
[STARTDATE] [char](8) NULL,
[ENDDATE] [char](8) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Destination](
[CHANGE_DATETIME] [datetime] NOT NULL,
[RECORD_TYPE] [char](1) NOT NULL,
[ID] [char](15) NOT NULL,
[STARTDATE] [char](8) NOT NULL,
[ENDDATE] [char](8) NULL,
CONSTRAINT [PK_Destination] PRIMARY KEY CLUSTERED
(
[RECORD_TYPE] ASC,
[ID] ASC,
[STARTDATE] ASC
)) ON [PRIMARY]
GO
insert into dbo.source values('1','1012013','NULL')
insert into dbo.source values('1','1012012','1012013')
insert into dbo.source values('1','1012011','1012012')
insert into dbo.source values('1','1012010','1012011')
insert into dbo.source values('2','1012012','NULL')
insert into dbo.source values('2','1012011','1012012')
insert into dbo.source values('2','1012010','1012011')
insert into dbo.source values('3','1012010','NULL')
GO
insert into dbo.Destination values (getdate(),'U','1','1012012','NULL')
insert into dbo.Destination values(getdate(),'U','1','1012011','1012012')
insert into dbo.Destination values (getdate(),'I','1','1012010','1012011')
insert into dbo.Destination values(getdate(),'U','2','1012012','NULL')
insert into dbo.Destination values(getdate(),'U','2','1012011','1012012')
insert into dbo.Destination values(getdate(),'I','2','1012010','1012011')
June 19, 2014 at 9:27 am
I think we need some help to understand what kind of help you need.
June 19, 2014 at 9:37 am
For id 1 the the destination table is loaded fine till the startdate of 01012012.
suppose if the load fails after that for some reason and meanwhile the source table is updated with two records
'01012012','01012013'
'01012013','NULL'
Now how can i reconsile the destination table with source.
Thanks
June 19, 2014 at 9:44 am
rxm119528 (6/19/2014)
For id 1 the the destination table is loaded fine till the startdate of 1012012.suppose if the load fails after that for soem reason and meanwhile the source table is updated with two records
1012012','1012013'
'1012013','NULL'
Now how can i reconsile the destination table with source.
Thanks
Not sure what you mean. Can you explain what you need help with?
Why do you have columns named like dates that are defined as char(8)? The data you posted for those columns is only 7 characters but looks strangely like some sort of non-date data.
_______________________________________________________________
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/
June 19, 2014 at 9:46 am
I hope I m not only the one here who cannot make any clue out of OP question...
What query brings data from source and destination?
What is your exepected results based on sample you have posted?
June 19, 2014 at 10:05 am
I am looking for some idea on how incremental loads can be handled in case of a failure in sql server or SSIS.
if the incremental load fails for a day or two, and the data in source keep accumlating
how can we sync both the source and destination.
I am just looking for idea not the acutal code.
lead zeros got truncated in the earlier data that i posted
insert into dbo.source values('1','01012013','NULL')
insert into dbo.source values('1','01012012','01012013')
insert into dbo.source values('1','01012011','01012012')
insert into dbo.source values('1','01012010','01012011')
insert into dbo.source values('2','01012012','NULL')
insert into dbo.source values('2','01012011','01012012')
insert into dbo.source values('2','01012010','01012011')
insert into dbo.source values('3','01012010','NULL')
GO
insert into dbo.Destination values (getdate(),'U','1','01012012','NULL')
insert into dbo.Destination values(getdate(),'U','1','01012011','01012012')
insert into dbo.Destination values (getdate(),'I','1','01012010','01012011')
insert into dbo.Destination values(getdate(),'U','2','01012012','NULL')
insert into dbo.Destination values(getdate(),'U','2','01012011','01012012')
insert into dbo.Destination values(getdate(),'I','2','01012010','01012011')
June 19, 2014 at 10:15 am
Ahhh I see..
Because you don't have a unique key, like an integer identity, you're concerned that if a load is in progress and fails, there'll be no way to tell where you got up to.
It looks like the best way to handle this will be with a day merge. So:
1: Check the latest date in the destination table
2: Get all data from source from this day forwards (>=).
3: Do a merge of the source data into the destination.
That way you will be able to reload if the run fails.
Cheers,
Jim.
June 19, 2014 at 10:17 am
You have some bizarre looking char dates there.
Are you looking to do INSERTS, or INSERTS and UPDATES or INSERTs, UPDATEs and DELETEs for your incremental load?
Do you have such columns as DateCreated and DateModified in your source table?
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
June 19, 2014 at 10:22 am
That's the crazy table with out the datecreated or modfied timestamp which brough me to this forum.
I am looking for both inserts and updates.
I am not particluar about the char dates.
I am only looking for possibility for the incremental where the source table doesn't have a proper timestamp column
Thanks
June 19, 2014 at 10:30 am
thanks for that suggestion Jim.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply