Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-10-16 : 09:12:58
|
| Vijay writes "I have a table which needs to be updated according to a change in its rows. I am using a CASE function to achieve this. However, I don't see the results despite the fact the process completes successfully displaying the number of records that have been transformed or copied. The first column in the table highlights a change with a letter. "C" for change, "A" for append and "V" for void or delete. I am attaching the script for your review and would appreciate if you could let me know where exactly am I going wrong. I am using SQL 2000 with Service Pack 2.'**********************************************************************' Visual Basic Transformation Script'************************************************************************' Copy each source column to the destination columnFunction Main() DTSDestination("ba_reccode") = DTSSource("Col001") DTSDestination("acct_no") = DTSSource("Col002") DTSDestination("firm_no") = DTSSource("Col003") DTSDestination("sub_no") = DTSSource("Col004") DTSDestination("rep") = DTSSource("Col005") DTSDestination("old_acct_no") = DTSSource("Col006") DTSDestination("zip") = DTSSource("Col007") DTSDestination("zip_plus4") = DTSSource("Col008") DTSDestination("foreign_zip") = DTSSource("Col009") DTSDestination("namelines") = DTSSource("Col010") DTSDestination("name_line1") = DTSSource("Col011") DTSDestination("name_line2") = DTSSource("Col012") DTSDestination("name_line3") = DTSSource("Col013") DTSDestination("name_line4") = DTSSource("Col014") DTSDestination("name_line5") = DTSSource("Col015") DTSDestination("name_line6") = DTSSource("Col016") DTSDestination("key1") = DTSSource("Col017") DTSDestination("key2") = DTSSource("Col018") DTSDestination("key3") = DTSSource("Col019") DTSDestination("key4") = DTSSource("Col020") Select Case DTSSource("Col001") Case "A" Main = DTSTransformstat_InsertQuery Case "C" Main = DTSTransformstat_UpdateQuery Case "V" Main = DTSTransformstat_DeleteQuery Case Else Main = DTSTransformstat_SkipRow End SelectEnd Function" |
|
|
ValterBorges
Master Smack Fu Yak Hacker
1429 Posts |
Posted - 2002-10-16 : 09:29:02
|
| This is a little of the topic. The technique I use for doing this is to bring the data to a stagging table. Next I run a delete query for all the records that exist in both tables (use a join to test) and are marked for delete. Then same for insert and update.I notice you have an extra quote at the end of function I assume that's a typo?Are you sure the queries are being execute and not the else statement in your case? I would verify that first maybe create a temp destination column to store which branch of the case is being executed and then compare branch versus col001.. |
 |
|
|
|
|
|
CASE statement