March 3, 2003 at 3:40 pm
I have a table in an old VMS Mainframe RDB database that I need to export/update into my SQL Server database daily. The intent is that the new data in the RDB database be updated into the SQL database so that it is kept up to date. The SQL Server table is used only for reading and is not updated by any other source than the DTS.
Currently I have DTS setup to drop the destination table, recreate it, and insert all the rows since there doesn't appear to be any way to update the existing data. I was satisfied initially with this situation but then I thought about backups...
My question is, if I am doing a full backup once a week and transaction logs daily, am I corrupting the database backups by dropping, recreating, and inserting the data? I'm not clear on if any of my actions are unlogged and if so, what the consequences are.
Should I delete the data from the table before the inserts instead of dropping and recreating?
March 4, 2003 at 8:41 am
Jackmang
You are not necessarily "corrupting" the database...but as you suggest, there might be other ways to achieve your desired result. Here are some issues which you have probably already considered.
1) Does the VMS data exist in such a way that you can export just the "new" information? This would reduce the load on the system and avoids the problem of a potential search for data at the time the old table was dropped.
2) If you do feel like it is best to completely replace the information, you might try using the TRUNCATE function to delete the data but leave the table structure. The TRUNCATE does not fill the log file.
3) There are some scenarios where loading the new information into a temporary table works best. The "new" information can be moved from the temporary table into the permanent one. Then - just TRUNCATE or DROP the temporary.
Just some food for thought
Guarddata-
March 4, 2003 at 8:51 pm
Thanks for the thoughts. I like the truncate idea. I also like the temporary table idea. If I could get the temporary table created from the RDB data it would then be pretty easy to run an insert query that would only insert new data. The problem is, I'm not sure how to go about creating a temp table in the SQL database from data in the RDB database. That is easy enough in T-SQL (creating a temp table from a query on another SQL table), but is it possible with an outside data source? I'm at home right now so I can't test this, but can I actually have a SQL step in the DTS that is something like: SELECT * FROM RDBTable into #SQLTempTable, and then in another step Do something like INSERT INTO SQLTable (SELECT * FROM #SQLTempTable WHERE FieldValue NOT IN (SELECT FieldValue FROM SQLTable)) or something like that?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply