Newbie alert: SQL Server 2000: How do I UPDATE data already loaded

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hi everyone,

I am working to learn DTS as a method to pull data from legacy Access
database(s) into a new SQL Server 2000 data structure, rather than using
Access to push the data there.

I have a table of lookup values (used in combo box [cbo] drop-down lists)
that includes fields:

ListDataID (long key; the data actually stored in the target tables)
ListName (varchar; used to group like list items--e.g. 'Country',
'UserStatus', etc.)
ListLabel (varchar; The individual plain-text values for each ListName that
the users choose--e.g. 'USA','Canada'; 'Active', 'Inactive', etc.)
ListOrder (int; The order in which ListLabels are displayed in the drop-down
cbos)

The data structures are similar enough that I've been able to successfully
load/transform the data from the Access table to the SQL Server table using
DTS.

Here's the problem: I have another table that maps ListLabel to
ListOrder_new, which I want to use to Update the existing ListOrder values
for only those records with ListName='Country'. IOW, what I'm trying to do
is to, after loading the data, reorder the Country values so that USA and
Canada will show up first and second (they are the most common choices in the
data set) in the cbos, then the rest in order as defined in the reordering
table.

I haven't been able to figure out how I might use the Lookup function to do
this during the initial transformation/load, because I'm only trying to
modify the List Order values for a subset (ListName = 'Country') of the whole
table, while leaving the List Order values for the other ListNames unchanged.

I've tried using the Data-driven Query Task, but either this is
inappropriate or I do not understand how it is supposed to work; what I've
tried to do is have the same connnection and table be the source (to get
ListLabel where ListName = 'Country') and destination to update 'ListOrder'
via the Lookup (using a connection to my map table), but I get a message
"source and dest can't be the same"

I have the feeling that, in an abudance of newbieness, I'm missing something
obvious (like how to do a conditional Lookup on-the-fly during the initial
transformation/load or some other post-load Update method or ???) and I know
all y'all will be able to tell me what the solution is. If the answer is
"get a new job," then so be it ;-)

Thanks in advance for the help.

Regards,

Ed
.

Relevant Pages

  • Re: Can you copy tables in DTS without having to drop the destination table?
    ... >I wanted to know if there is a option in DTS where I can copy new data ... >destination is becuase replication is running on the destination DB ... depending on the result of a lookup. ... DTS - http://www.sqldts.com PASS - the definitive, global community for SQL Server professionals ...
    (microsoft.public.sqlserver.dts)
  • ASP creating an Excel Spreadsheet getting an 80004005 error....
    ... The table that is being read from the SQL Server has about 50 columns to it. ... there are 7-8 fields that need to do a lookup like this. ... spreadsheet had been started. ... It seems to get through about 90-95% of the records, then just hangs for about 20-30 seconds then gives this message. ...
    (microsoft.public.inetserver.asp.db)
  • ASP creating an Excel Spreadsheet getting an 80004005 error....
    ... The table that is being read from the SQL Server has about 50 columns to it. ... there are 7-8 fields that need to do a lookup like this. ... spreadsheet had been started. ... It seems to get through about 90-95% of the records, then just hangs for about 20-30 seconds then gives this message. ...
    (microsoft.public.inetserver.asp.general)
  • [patch][rfc] ddds: "dynamic dynamic data structure" algorithm, for adaptive dcache hash table sizing
    ... Describe the "Dynamic dynamic data structure" algorithm, ... check for each dentry insertion/deletion, and calls the resizing in-line from ... More commonly, it will also add a level of pointer indirection, as ... Enter RCU read-side critical section (covering the whole lookup operation). ...
    (Linux-Kernel)
  • [patch][rfc] ddds: "dynamic dynamic data structure" algorithm, for adaptive dcache hash table sizing
    ... Describe the "Dynamic dynamic data structure" algorithm, ... check for each dentry insertion/deletion, and calls the resizing in-line from ... More commonly, it will also add a level of pointer indirection, as ... Enter RCU read-side critical section (covering the whole lookup operation). ...
    (Linux-Kernel)