Update a table with date when there are new records in another table.

Question

Update a table with date when there are new records in another table.

hegdesuchi

Hall of Fame

Points: 3047
More actions
September 16, 2015 at 12:57 pm

#304735

Hello,
I have a scenario where I have to Update a table with date when there are new records in another table
For example:
I load ODS table with the data from a file in SSIS. the file has CustomerID and other columns.
Now, when there is new record for any customerID in Ods, then Update the dbo table with the most recent record for every CustomerID(i.e. update the date column in dbo for that customerID). Also Include an Identifier that relates back to the ODS table. How do I do this?
Regards,
Chits

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

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Use a trigger.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

hegdesuchi Hall of Fame Points: 3047 More actions · Answer 2

thank you :-).

how do I add an identifier back to ODS table? what does this mean?

Regards,

Chits

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

Please share DDL and sample data to be able to give a better answer.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

hegdesuchi Hall of Fame Points: 3047 More actions · Answer 4

Sorry for that.

Scenario:

ODS table :

CustomerID column1column2column3

1 abc abc abc

2 xyz xyz xyz

Dbo table:

CustomerID column1column2column3 date

1 abc abc abc 1/1/1900

2 xyz xyz xyz 1/1/1900

Now If I get any new record for customerID "1" in ODS table, then CustomerID"1" in dbo should be updated with getdate() in date column.

Result expected:

ODS table :

CustomerID column1column2column3

1 abc abc abc

2 xyz xyz xyz

1 abc xyz def

Dbo table:

CustomerID column1column2column3 date

1 abc abc abc getdate()

2 xyz xyz xyz 1/1/1900

Also, i need to set an identifer that relates back to ODS table. I am not sure what does this mean?

hegdesuchi Hall of Fame Points: 3047 More actions · Answer 5

also,

another scenario is,

Scenario:

ODS table :

CustomerID column1column2column3

1abcabcabc

2xyz xyz xyz

Dbo table:

CustomerID column1column2column3 date

1abcabcabc 1/1/1900

2xyz xyz xyz 1/1/1900

Now If I get any new record for customerID "1" in ODS table, then CustomerID"1" in dbo should be updated with getdate() in date column.

Result expected:

ODS table :

CustomerID column1column2column3

1abcabcabc

2xyz xyz xyz

1 abc xyz def

Dbo table:

CustomerID column1column2column3 date

1 abc xyz def getdate()

2xyz xyz xyz 1/1/1900

The latest record has to get updated in dbo for that customerID

Also, i need to set an identifer that relates back to ODS table. I am not sure what does this mean?

LinksUp SSCertifiable Points: 6526 More actions · Answer 6

hegdesuchi (9/16/2015)
also,
another scenario is,
Scenario:
ODS table :
CustomerID column1column2column3
1abcabcabc
2xyz xyz xyz
Dbo table:
CustomerID column1column2column3 date
1abcabcabc 1/1/1900
2xyz xyz xyz 1/1/1900
Now If I get any new record for customerID "1" in ODS table, then CustomerID"1" in dbo should be updated with getdate() in date column.
Result expected:
ODS table :
CustomerID column1column2column3
1abcabcabc
2xyz xyz xyz
1 abc xyz def
Dbo table:
CustomerID column1column2column3 date
1 abc xyz def getdate()
2xyz xyz xyz 1/1/1900
The latest record has to get updated in dbo for that customerID

You will need to create a trigger, as was suggested. You can google that to find 1000's of references on the syntax for creating of the trigger.

Then you will need something like this in the trigger:

CREATE TRIGGER [ods].[Table1Trigger]

ON [ods].[Table1]

AFTER INSERT

AS

BEGIN

SET NOCOUNT ON;

Update Table2 SET

Table2.Column2 = i.Column2

Table2.Column3 = i.Column3

Table2.Date = GetDate()

From [dbo].[Table2] Table2

Join inserted i

on i.CustomerID = Table2.CustomerID

END

Also, i need to set an identifier that relates back to ODS table. I am not sure what does this mean?

Neither do I.

__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

ScottPletcher SSC Guru Points: 101098 More actions · Answer 7

If instead of sync'ing up just once a day, or some other slow schedule, you want an up-to-the-second value, why not just get the value from the actual table itself instead of copying it into another table?

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

hegdesuchi Hall of Fame Points: 3047 More actions · Answer 8

Hi Guys,

Thank you very much for your replies. I found below solution for my problem..

SELECT A.*

FROM YourTable A

JOIN (

SELECT CustomerID , MAX(Id) MaxId FROM YourTable GROUP BY CustomerID ) B

ON A.CustomerID = B.CustomerID AND A.Id = B.MaxId

i.e for

ODS table :

ID CustomerID column1column2column3

1 1 abc abcabc

2 2 xyz xyz xyz

3 1 abc xyz def

By using above query I get

ODS table :

ID CustomerID column1column2column3

3 1abcxyz def

2 2xyz xyz xyz

I insert the values into temp table and then merge with dbo table on customerID. this solved my problem..

Thank you very much again. I will keep Trigger in mind as this might help me in future.