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 |
|
dogli
Starting Member
32 Posts |
 Posted - 2002-10-07 : 11:58:11
|
| With the help of guru here such as vobrolk and nr, I can successfully automate the update process of my local Sql server based on a very very big Oracle database.Thanks for all of your help.But I have one more question. My main task here is to automatically update a SQL server table based on the Oracle database. Then I have serval applications will automate some process based on the SQL server table.What I am doing now is to automatically delete the old sql server table first, and then automatically update it based on a query. It works well now, but my concern is that it will take maybe 30 to 50 seconds to finished the task, and during these peoriod, all of my applications cannot run again this table. Althogh I schedule this job to start at 1:00AM in th evening, and very likely, there are no employees to use these applications.(all of these applications are intranet applications.Another way I can do the same thing is to UPDATE the old sql server table only(don't delete it first). But I need to write a very very complicated query because I need to check some fields in Oracle tables, and if some flag changes, I will update sql server table, otherwise, keep it unchanged. I am new to develop such enterprise application, do you guys think I can do in the first way?Again,thanks for all of your help. |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-10-07 : 13:23:43
|
| Could you create the "updated table" as a different name, load all of your data etc into it, delete the old table, and rename the new table?Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
 |
|
|
setbasedisthetruepath
Used SQL Salesman
992 Posts |
Posted - 2002-10-07 : 14:38:48
|
quote:
Althogh [sic] I schedule this job to start at 1:00AM in th evening, and very likely, there are no employees to use these applications.
As long as your application is "very likely" not a 24x7 app, you will not be able to delete the existing table and insert from an Oracle table. You must either force all users off, or write an update query. I suspect if you try to write such a query you'll find it's not quite as complex as you might currently think.Jonathan{0} |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-10-07 : 21:44:13
|
| The dual tables sounds like a good idea.How much down time can you afford?With a delete and rename there will be a time when any access will fail - but much less than taking it offline to do the insert.A complete refresh like this is a lot cleaner as any problems are cleared out - so even if you go for an update still leave in the possibility of a refresh and maybe do it once a week or so.Not sure what happens if you try to delete a table while people are accessing it - probably waits, easy to test though.You might instead condsider getting the users to access a view which points to the table - swapping tables then becomes changing the view.If all access is via stored procs which you control then you can do this with no down time at all - just have the table to access in a table and the SPs have an if statement to access the correct table.I've done this sort of thing before with databases and web sites where the database to access is held in a table and each SP is duplicated in the two datbases and also has a shell in a third which actually gets called by the client.So if the Sp in the working databases iscreate proc spTest@i int ,@s varchar()as...then the shell would becreate proc spTest@i int ,@s varchar()asif (select db from ActiveDatabase) = 1 exec db1..spTest @i, @selse exec db2..spTest @i, @sgo(shell SPs all automatically generated)In this way the new data can be sent to the web site and the access switched to the new database with no down time (and back again if something goes wrong).It does though introduce an overhead in every access.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
|
|
which idea is better?