Stephen wrote: I am porting over some information from an Access 97 DB to MSSQL 2000 and am using scheduled DTS (data transformation services) to do it.
Problem: on the first run, it creates the table fine, but when executing manually or on a schedule, it does not update the existing table... The only thing I have thought of to remedy this is to use the DTS 'Execute SQL Task' to drop the table before creating it again... but this can't be the best way to do this. I am creating one table in MSSQL 2000 out of selected data from two tables in Access 97. There are around 2000 records being brought over. The data I am pulling from these two tables emulates how the data will be structured once the legacy system is phased out. I would like to have DTS update the information in our new system daily. I'm not against dropping and recreating, but I can foresee problems if someone happens to query the DB in the middle of an update. here's an update: apparently the DTS is actually taking place, it seems to be appending the rows rather than updating them though... As cruddy as that is, I actually feel a little better, because I have something to go on. Any ideas? Tim responds: Sorry for the late reply, I get the digest version of WDVL, and not over the weekend. :-) You'll need to look at what kind of data you're transferring. Basically you have the choice of copying data (like your current DTS is doing), which means it just appends the records from the Access tables to the SQL table. Since it's already doing this it would indicate that you aren't using primary keys; otherwise the DTS would croak about data integrity violations on the second (and future) runs. I assume you *don't* want the records appended, since you'll end up with a bunch of duplicate data. Also assuming you don't want to drop and recreate the table each time, you've got three problems to solve: 1. If the data record from the Access tables already exists in the SQL table, you just want to update the SQL table with the data from the Access tables. 2. If the data record from the Access tables doesn't exist in the SQL table, you need to insert it into the SQL table. 3. If a data record was deleted from the Access tables, but still exists in the SQL table, you'll need to figure out how to grab it and delete it from the SQL table. I don't think you can do this kind of updating with a DTS package...after building several of them to mess around with, I couldn't find any options that allowed this kind of work. So you'll have to do something else. I've done stuff like this before and here's how I solved it: 1. Set up primary keys of some sort on both the SQL and Access tables. 2. Set up an additional column in the SQL table - I usually make mine a "bit" field and name it "To Be Deleted". 3. Set up a DTS package that copies the data from the Access tables to a temporary SQL table (drop the temp table first if it pre-exists). This part is similar to what you're already doing. 4. Write a stored procedure that parses the temp table and compares it to the existing SQL table. It should follow logic something like this: A. set the "To Be Deleted" field of all the records in the SQL table to 1. B. read the first(next) record from the temp table. You'll need to use a cursor for doing this. C. if there's a matching PK in the SQL table, update the record in the SQL table with data from the temp table, making sure to set the "To Be Deleted" field back to 0. D. if there's not a matching PK in the SQL table, insert the data from the temp table into the SQL table, making sure to set the "To Be Deleted" field to 0. E. loop until you're out of records in the temp table. F. all the records left in the SQL table marked 1 in the "To Be Deleted" field were already deleted from the Access tables, so you need to delete them from the SQL table now. G. at this point, your SQL table should be up to date, and all the "To Be Deleted" fields should be 0. 5. Set up a daily task that runs the DTS first, and upon successful completion, runs the stored procedure. Whew. Yucky, isn't it? Not very glamorous. Maybe other listers have better ideas. :-) Usually things are done the other way around...SQL publishing/replicating to Access. That's a lot easier. Tim ___________________________ Tim Furry Web Developer Foulston Siefkin LLP ____ � The WDVL Discussion List from WDVL.COM � ____ To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED] Send Your Posts To: [EMAIL PROTECTED] To change subscription settings to the wdvltalk digest version: http://wdvl.internet.com/WDVL/Forum/#sub ________________ http://www.wdvl.com _______________________ You are currently subscribed to wdvltalk as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED]
