HI friends,
I am retrieving oracle data(linked server) to my local sql server 2005. Initially I want to have duplicate copy of the oracle data to my local server. I have created linked server and inserting to my local table(exact replica). This will run for every 2 minutes(as per my client requirement) to get newly added records.
But, I want to retrieve specific columns from the replicated table and insert into other local tables. i have written a SP to do this. some columns are storing in other tables to maintain normalization. this should also run for every two minutes.
I have written a class (c#) to retrieve replica table and passing parameters to SP and inserting to my local tables in normalized form.
But this is taking 10 minutes to complete my process to insert 1500 records. but my client insist to reduce the speed to run for every 2 mins.
is it correct way wat i am doing? or any other solution is there?
pls suggest me.
thanks in advance
You have supplied very little technical info on exactly how you are accomplishing this task. Please elaborate.
I will tell you, though, if you are pulling a bunch of queried records out of the database into a c# collection and inserting them back into the database one at a time, you ARE going about it the wrong way. :)
You should be writing this code in T-SQL or SSIS, not C#.
You should definitely make use of insert statements that insert multiple records at a time. An example is: insert into some_table (col1,col2,col3) select col1a, col2a, col3a from another_table.
