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

Question

Post reply

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: 183686 More actions · Answer 1

Luis Cazares

SSC Guru

Points: 183686

September 16, 2015 at 1:05 pm

#1827186

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

hegdesuchi

Hall of Fame

Points: 3047

September 16, 2015 at 1:14 pm

#1827188

thank you :-).

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

Regards,

Chits

Luis Cazares SSC Guru Points: 183686 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 column1 column2 column3

1 abc abc abc

2 xyz xyz xyz

Dbo table:

CustomerID column1 column2 column3 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 column1 column2 column3

1 abc abc abc

2 xyz xyz xyz

1 abc xyz def

Dbo table:

CustomerID column1 column2 column3 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 column1 column2 column3

1 abc abc abc

2 xyz xyz xyz

Dbo table:

CustomerID column1 column2 column3 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 column1 column2 column3

1 abc abc abc

2 xyz xyz xyz

1 abc xyz def

Dbo table:

CustomerID column1 column2 column3 date

1 abc xyz def getdate()

2 xyz 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 column1 column2 column3

1 abc abc abc

2 xyz xyz xyz

Dbo table:

CustomerID column1 column2 column3 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 column1 column2 column3

1 abc abc abc

2 xyz xyz xyz

1 abc xyz def

Dbo table:

CustomerID column1 column2 column3 date

1 abc xyz def getdate()

2 xyz 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: 100262 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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

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 column1 column2 column3

1 1 abc abc abc

2 2 xyz xyz xyz

3 1 abc xyz def

By using above query I get

ODS table :

ID CustomerID column1 column2 column3

3 1 abc xyz def

2 2 xyz 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.