Also, watch out for black and white statements, like:

dynamic SQL is to be avoided.

Generally, for regularly repeated tasks that you want maximum performance out of, this is true. When...

I have had need to check for common errors (for example, data where the user has included a EOL character in the middle of a field) in large data files....

Talking more about situation three.

First, just to be completely pedantic, step 4 in your example seems to have the table names reversed, so that instead of copying the latest data...

Odds are there are other things you have to tweak between the two environments. If those things are consistent, maybe you should look at automating your find/replace routine. ...

I ran the two statements together as a batch. Therefore, the plan included both statements. When I say that the "order by" statement had a percentage of 96%,...

Forgot to add this when I recreated my post (first attempt disappeared when I hit "Preview", probably because of some sort of time-out thing).

You need "TOP 1" in the "select...

I expected little difference between these two queries, performance-wise. But, being the curious soul I am, I decided to try it out, with the following code:

DROP TABLE #T_PEOPLE
GO

CREATE TABLE...

Typed this up once, and the browser seems to have eaten it. I'll try to do it a bit shorter this time.

You probably want to run this query in...

WRT limiting the select options:

You could always consider "all" as a special option - pass a flag instead of passing the actual user ids. Should be a bit faster,...

My NULLIF - RTRIM version and alzdba's ISNULL - LTRIM version should be equivalent in efficiency - just an example of two different people looking at things in a slightly...

EDIT: Upon a second read, this is identical to the ISNULL version pposted earlier - sorry for the redundancy.

One last version:

select case when NULLIF(RTRIM(Record1),'') IS NULL THEN 0 ELSE...

Just a thought. I've worked places where the system support staff would handle system updates, updates of common applications, and suchlike outside of normal working hours. Does your...

An approach I used on a weekly data load a while back might be useful:

The data we received was, essentially, a change log from the true owner of the data....

First, please confirm:

A previous poster suggested segregating the data by time period. Given that you note that the data will reach a maximum of 5,000,000 records, and your weekly...

Is the issue how to execute the proc from Oracle, or getting the proc itself right?

Quick pseudo-code for ins/upd proc:

create procedure InsUpd
(@pkey_field1
,@pkey_field2
,@insert_only_field1
,@insert_only_field2
,@update_field1
,@update_field2
)
AS

IF (EXISTS (SELECT * from table
   ...