One solution would be to split out the common and standard data, and make those into tables, where the end-user does NOT have access to modify the structure, then add...

Books Online has directions on how to do that, under "declare cursor". Also has samples and examples you can copy.

I don't know your tables, so I can't give you...

You can use the Pivot function for that.

Two things to add to that:

If you decide to go one-per-customer, make sure to set up your model database (system databases) so that it's got everything a new customer needs....

Separate databases. Lots of reasons.

Makes it easy to split it up onto separate servers to scale out.

When a customer calls you and wants you to restore "their" database to...

Dana Bibeault (9/15/2008)

---

Mixing environments has never sat well with me either. We do intend to use SQL Mail (or some better way to get notifications on data loads,...

Is this a one-time import or something you will be doing over and over again?

First, have you checked the settings on your server and made sure it will allow remote connections?

Second, is the login being used set up correctly?

You might be able to if your modifications are in some sort of source control application. Are they?

I think I can rewrite that into a set-based solution. To be sure, I need table definitions and some sample data for all the tables in the query.

That seems...

Actually, after re-reading your post, I realized you currently have 56-thousand records and might expand up to 500-thousand. I thought you already had 500-thousand. That means you have...

Basically, what you have is a version of the "one true look-up table". Lots of developers and DBAs have this realization that they can store meta data in one...

Are the databases on the same server or on different servers?

If they are on the same server, you need to use a "3-part name" in the query.

If they are on...

You're going to need to have a cursor step through the rows of your table and call sp_send_dbmail once for each row.

Cool. Glad you got that working.