They should copy the production databases to their dev server and then use those. That's safer, too.

Instead of all the hard-coded dates, it looks like you could use a CTE like this:

declare @Date datetime;

select @Date = dateadd(day, datediff(day, 0, getdate()), 0); -- Removes time from date

;with...

I don't believe there's going to be an actual difference in execution time for the two queries. Theoretically there might be, because of the one with the * having...

While it is possible that it needs every row and column every time in those, it's sloppy coding nonetheless.

Are you actually in a position to do anything about it if...

I doubt there's any sort of automatic tool that will migrate these. It's probably a manual process.

I'd say the main thing to keep in mind is test, test, test,...

Take a look at OpenRowSet in Books Online. You can use that to query CSV files, text files, etc.

IsNull (or Coalesce) is what you want. Take a look at them in Books Online.

The specific thing you're trying to do with them looks like:

UPDATE prices SET suppid =...

Is this something where you can provide the table create scripts and some insert statements for sample data? Ideally, also include the desired output.

I can see a few things...

No, you're not missing anything. That solution just plain doesn't work. I missed a bit when looking at it. That's what I get for rushing.

You can specifically grant the right to create databases.

Take a look at "Grant" in Books Online. From there, you'll want to go to "Grant Database Permissions".

On the same dataset, without updating to zero, the complex Case statement took 8:57 to run.

GilaMonster (4/6/2009)

---

Paul White (4/6/2009)

---

My entirely personal view is that if someone is able to talk a little about things like the 'halloween problem' in an interview, it shows they have...

Jeff Moden (4/6/2009)

---

GSquared (4/6/2009)

---

Jeff Moden (4/6/2009)

---

Here's a perfect example...

http://www.sqlservercentral.com/Forums/Topic688949-8-1.aspx

My answer would have been... "Thanks for the setup code... please post the code that you've tried so far so we can...

You're right. I was in a rush yesterday, and forgot to take into account the change from null to zero would affect the join math.

So far as I can...

If you do this:

update #test

set col1 = isnull(col1,0),

col2 = isnull(col2,0),

col3 = isnull(col3,0),

col4 = isnull(col4,0),

col5 = isnull(col5,0),

col6 = isnull(col6,0),

col7 = isnull(col7,0),

col8 = isnull(col8,0)

;with CTE...