June 2, 2005 at 5:10 am
I am trying make duplicate data from an AS400 database to a Sql Server Datatbase the tables are Identical. I have already written the sp to insert new record from the as400. I a now stumped as to how to compare the 128 columns which change from table to table throught 21 tables. checksums do not work.
We use a stock control system on an AS400. The records on the AS400 need to be cleaned up and physically remove those records marks for deletion. My boss wants a sql database to hold all the deleted and non deleted records. These databases are front ended with ASP.net application to review records and look at deleted, pending deletion, and current records. In the stock library on the IBM machine contains 21 tables all 21 tables contain different combinations of the one master table. Any of the 21 tables may change, or have records inserted or deleted. I have written procs that retieve table names and column names from the system datatables. I have written a stored proc to insert new records from the AS400 to the Sql Database in all 21 tables. I now need to take all changed records from the as400 and update the records in the Sql database to keep the copy upto date.
SQL Developer
James
Access Accounting Ltd
June 2, 2005 at 5:28 am
Can you elaborate a bit more on what you are trying to do? ..and perhaps also why? (it gives a good background to know the reasoning behind ones attempt to solve a problem)
/Kenneth
June 3, 2005 at 4:25 pm
james,
You know, probably the easiest solution is to delete the SQL record and re-import it from the AS400. This can be done pretty easy with a couple pieces of information.
Unique Key. Each record from the AS400 must have a unique key (by row) that is moved into the SQL table during the insert.
LastUpdateDate. Each record from the AS400 must have a LastUpdateDate
@LastExtractDate. This date gets updated once the extract from AS400 to SQL is complete. Usually this is stored in a table, then that record is updated at the end of each extract.
With those pieces of information:
1) Delete all SQL records where AS400 record has been updated since the previous @LastExtractDate.
delete s
from SQL s
JOIN AS400 a on s.UniqueKey = a.UniqueKey
where a.LastUpdateDate > previous @LastExtractDate
2) Insert all AS400 records into SQL that don't already exist.
Insert SQL
select a.*
from AS400 a
LEFT JOIN SQL s on s.UniqueKey = a.UniqueKey
where s.UniqueKey is null
Probably the best way to achieve this join is to link between the databases, but you could also use a SQL Staging table for the AS400. Again, use the @LastExtractDate to grab all updated record:
Insert AS400
select *
from Linked.AS400
where LastUPdateDate > previous @LastExtractDate
BTW, what are you using to connect to the AS400 DB2?
Signature is NULL
June 6, 2005 at 2:21 am
ASW is an off the shelf system that sadly has no last updatedcolumn in fact it has no dates in this set of tables. Thank you for the help given but any more suggestions would be greatfully appreciated.
SQL Developer
James
Access Accounting Ltd
June 6, 2005 at 6:15 pm
Yikes...then I'd say you're SOL for an easy solution, expect to recreate the archive table every time.
The only other option I see is to compare each value for each row from one table to the other, and do the update accordingly.
Depending on the size of your table, this could potentially be more expensive than a full replace.
Good luck, and shame on the vendor for not providing basic audit columns!
cl
Signature is NULL
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply