Your syntax isn't quite right.  Try this:

insert into sales_copy(stor_id,ord_num,ord_date,qty,payterms,title_id)

select stor_id, ord_num, ord_date, qty, payterms, title_id from sales

Does the insertion order matter?  Can't you just create a clustered primary key to force the display order?

Apart from the table names being plural (I think most DB developers tend to stick to singular), your naming convention looks absolutely fine to me.  Having a 'LastUpdated' column in...

Rather than DROP/CREATE, why don't you just TRUNCATE the table?  Not that that will save much time though ...

Deterministic or not, the argument (and therefore the result) of the CONVERT function varies every time the function is called.  If 250,000 rows are returned, that's approx 40 million invocations...

Oh good.  Well, if there's a lot of data, that's a lot of CONVERTs - approx 15 per row returned.  I reckon it might all add up.  Missing links don't...

Do you mean why is it faster doing nothing than doing CONVERTs??

From what you've said, I think the temp table / table variable (each should be tried to see which is faster) is the way to go too.

Can you change the back-end field types to nvarchar, rather than doing the converts?  I'm guessing not.

So you actually want to DELETE the data from table A that's already in tableb?

If so

delete from TableA

where tableA.ID in (select ID from tableB)

should do the trick.  Obviously, I'm assuming...

Also, getting rid of the in-line CONVERTs would speed things up.

There's probably a fancy way, but you could always create a new ID field in table Y (identity, seed = 12000 (or whatever you need your numbering to start at),...

Could this be a security issue?  If it runs when you execute it manually, yet not when it is executed by the SQL Server Agent, that is what I would...

Can you overwrite the path with one that is valid before the error message appears?  Sorry, I should know, but I haven't done this for a while.

Are you sure it's a 'him'?

Only kidding ladies