Updating Table 2 with Table 1 from Lastmodifieddate

Question

Post reply

Updating Table 2 with Table 1 from Lastmodifieddate

ALIF-662928

SSCertifiable

Points: 6054
More actions
February 17, 2010 at 9:06 am

#145294

We have two tables,
Table 1 Customer1
CustomerID, ISActiveFlag , LastModifiedDate
--------------------------------------------
1 12009-11-16 15:06:00
2 12009-11-16 15:10:00
3 12009-11-17 15:04:00
Table 2 Customer2
CustomerID, ISActiveFlag , LastModifiedDate
-------------------------------------------
1 12009-11-16 15:06:00
2 12009-11-16 15:10:00
So whenever there is any change in the lastmodifieddate in Customer1 Table, we need to update that with the Customer2 columns.
Like
get the max lastmodifieddate from Customer2 table and pull all the records >= lastmodifieddate from Customer1 Table.
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Wesley Norton SSCrazy Points: 2827 More actions · Answer 1

Let me know if I am on the right track with this:

if object_id('tempdb..#customer1') is not null drop table #customer1

if object_id('tempdb..#customer2') is not null drop table #customer2

Create table #customer1 (customerid int not null,isactiveflag bit not null,lastmodifieddate smalldatetime not null)

Create table #customer2 (customerid int not null,isactiveflag bit not null,lastmodifieddate smalldatetime not null)

--Table 1 Customer1

--CustomerID, ISActiveFlag , LastModifiedDate

--------------------------------------------

insert into #customer1 values ('1','1','2009-11-16 15:06:00')

insert into #customer1 values ('2','1','2009-11-16 15:10:00')

insert into #customer1 values ('3','1','2009-11-17 15:04:00')

--Table 2 Customer2

--CustomerID, ISActiveFlag , LastModifiedDate

-------------------------------------------

insert into #customer2 values ('1','1','2009-11-16 15:06:00')

insert into #customer2 values ('2','1','2009-11-16 15:10:00')

select * from #customer1 c1 inner join #customer2 c2 on c2.lastmodifieddate >= c1.lastmodifieddate and c2.customerid = c1.customerid

Paul White SSC Guru Points: 150467 More actions · Answer 2

Alif,

I've read your question several times now, and given it some thought...but I'm afraid I can't understand what you are asking for here.

Please take another shot at explaining what you need. If I can understand it, I'll post a solution.

Thanks!

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

John Rowan SSC Guru Points: 56440 More actions · Answer 3

I'm with Paul here. I'm not quite sure what you want. Wesley has been kind enough to post your sample data in a readily usable form, but it would help if you could post an example of how you'd want the data to look in the event of an update.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Paul White SSC Guru Points: 150467 More actions · Answer 4

Thanks John - I should have acknowledged Wesley's contribution in my previous post.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

ALIF-662928 SSCertifiable Points: 6054 More actions · Answer 5

Very wonderful of all you guys, Wesley, Paul,John...

Actually i am creating a package, what the package needs to do is, whenever there is any change in the S1 table it should update the S2 table on any inserts/update on the S1 table, more like a transactional replication,

And in this process, it should not change the lastmodifieddate column in the S2 instance when this package is run. This are not small tables, i have like 20 tables to do like this in one package everyday so cannot do truncate and insert for this for all this, avarage table size is some 200 million rows-400million rows.

so thought of this option, Also cannot use T-replication for this tables as we have some other plans in the schema pretty often.

its basically run everyday

1)It should pick up all records where LastModifiedDate is greater than

2)It should update if the records exists

3)If the record does not exist, it should insert

The only thing it should not do is change the lastmodifieddate field when the package is run, just insert whatever date it is in the S1 to S2.

Hope i have explained it,

Thanks for all your valuable suggestions.

John Rowan SSC Guru Points: 56440 More actions · Answer 6

So are you having a specific problem with implementing this or do you just want some general how-to's?

So, do all of your table have a date column that you can key off of?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

ALIF-662928 SSCertifiable Points: 6054 More actions · Answer 7

Yes, i am having problems in creating this in ssis package, if any one can send me their email, i can send the sample db bak file to you, and it will be great if you can send me a sample package for two tables,

John Rowan SSC Guru Points: 56440 More actions · Answer 8

I can't put together a package for you now as I'm leaving for the day, but at a high level, here's one way to do it.

1. You'll need a task in your Control Flow to get the MAX date from your destination table. Put this into a variable so we can use it in our Source queries. Info on how to do this is here.

2. Data flow task with the following components:

2.1 - OLE DB Source - Set the Data Access Mode to SQL Command. Put your source query in the window and use the Parameters to put your variable from #1 into the query.

2.2 - use the Slowly Changing Dimension transformation. This will walk you through a Wizard to configure the actions. It's pretty simple. Use the PK value as your business key, set all other columns as changing attributes. If you have one that you don't want to change, set it as a fixed attribute and set it not to fail the component. The SCD wizard will create all of the other tasks/destinations that you will need.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden