Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Checking for updated rows Expand / Collapse
Author
Message
Posted Monday, February 11, 2013 9:02 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 14, 2014 7:06 AM
Points: 321, Visits: 569
In Andy Leonard's article "Adding Rows in Incremental Loads - Level 3 of the Stairway to Integration Services" he goes over adding 'Lookup' object and checking for modified rows. I decided to do some training... using our data.. and it generated an error due to the data type, in my table, was real. So just how do we test to see if the data has changed if we have a column that is real? Why will it not work on real data?

The error...

TITLE: Microsoft Visual Studio
------------------------------

Cannot map the lookup column, 'New_Safety_Stock', because the column is set to a floating point data type.

------------------------------
BUTTONS:

OK
------------------------------
Post #1418502
Posted Monday, February 11, 2013 2:08 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:35 AM
Points: 5,024, Visits: 11,754
dwilliscp (2/11/2013)
In Andy Leonard's article "Adding Rows in Incremental Loads - Level 3 of the Stairway to Integration Services" he goes over adding 'Lookup' object and checking for modified rows. I decided to do some training... using our data.. and it generated an error due to the data type, in my table, was real. So just how do we test to see if the data has changed if we have a column that is real? Why will it not work on real data?

The error...

TITLE: Microsoft Visual Studio
------------------------------

Cannot map the lookup column, 'New_Safety_Stock', because the column is set to a floating point data type.

------------------------------
BUTTONS:

OK
------------------------------


You usually do a lookup on the PK of your table. Andy's article suggests something similar, I think: check whether a matching row exists (match on PK) - if it does, update it, otherwise, insert it.

What the article does not do is check individual columns to see whether they have changed.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1418630
Posted Monday, February 11, 2013 3:14 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 14, 2014 7:06 AM
Points: 321, Visits: 569
I changed to PK, thanks. I will have to look back and see if he stated anything about using PK... I would not think that e-mail would be a PK, since folks tend to have more than one.
Post #1418652
Posted Monday, February 11, 2013 3:19 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:35 AM
Points: 5,024, Visits: 11,754
dwilliscp (2/11/2013)
I changed to PK, thanks. I will have to look back and see if he stated anything about using PK... I would not think that e-mail would be a PK, since folks tend to have more than one.


I will agree with that - but he does state that he has decided to match the first one he finds and update just that one row. In a way, that makes the e-mail column in the example effectively a PK.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1418653
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse