Merge vs. Truncate/Insert 1

[ca8msm]

Hi,

I was wondering if someone could explain what the differences between the following would be and which one we should use.

We basically have a table that we refresh once a week. At the moment we truncate the table and then run an insert command to load the new data.

I've recently come across the Merge command and was wondering if we should use this instead to update the existing data and insert the new records.

Is there a difference in speed or is it simply user preference to which one we use?

Oh, and our data is "screened" so there are no dupicates etc. (Just in case anybody was going to point out the pitfalls of a Merge command failing!)

Thanks

----------------------------------------------------------------------

Need help finding an answer?

Try the search facilty (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.

 

[sem]

It highly depends on the amount of freh data you need to insert. If the estimated amount of rows to update is relatively small then loading the whole date to truncated table may be less efficient than merge, especially when you have declaratve constraints on that table.

Regards, Dima

http://seminihin.narod.ru

 

[ca8msm]

OK - thanks for the response.

----------------------------------------------------------------------

Need help finding an answer?

Try the search facilty (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.