The above is essentially the final code. It ran great (26 seconds) and the discrepency in the results vs my original code was a bug in my original code.

Kudos...

Don't know if there's a more elegant solution, but this works:

declare @str1 varchar(8000)

declare @str2 varchar(8000)

SET @str1 = 'insert into Appts( ID, CarID) select P.ProspectID, C.CarID FROM [2004 UIL Prospects Appointments]...

It has something to do with string length. When I changed the code to:

declare @str varchar(8000)

SET @str = 'insert into Appts( ID, CarID) select P.ProspectID, C.CarID FROM [2004 UIL Prospects...

OK. The code stops at Carrier #88:

insert into Appts( ID, CarID) select P.ProspectID, C.CarID FROM [2004 UIL Prospects Appointments] P cross join Carriers C where  1= (case

When ID =...

You mean this ?

declare @str varchar(8000)

@str = 'insert into Appointments( ID, CarrierID)

select PK_apointment, CarrierID and 

 YourTable

 crossjoin

 Carriers

where

 1= (case '

select @str = @str + 'When ID = ' cast(columnID -...

Hmmm... 6:36 v 00:26. There's a difference in the results between the two of about 1,000 entries in the Appointments table. I'll have to see if I can figure out...

"If the schema changes you can generate the SQL dynamically." Oy... I suppose I could, I'll have to think on that one. That's a lot of dynamic sql.

"BTW: you are...

Noeld -

Carriers is already populated. It's a fixed table. The only time it's updated is when a new Carrier is introduced in the "UIL Prospects Appointments" table (The "2004" in...

I look up the column name in the Carriers table to get the CarrierID (CarID).

I realized that I was processing EVERY cell (row,column), not just the ones with a...

The DDL for the simple tables:

CREATE TABLE [dbo].[Appointments] (

 [ID] [int] NULL ,

 [CarID] [int] NULL

) ON [PRIMARY]

GO

CREATE TABLE [dbo].[Carriers] (

 [Carrier] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

 [CarID] [int] NOT NULL

)...

Here's the code. I'm working on getting the CarID into the table variable up front so I don't do that repetitively in the second WHILE loop. If anyone can figure...

I have no trouble getting the column names from syscolumns, it's the iterating over the actual data columns (102) times 59,000 rows and determining for each one whether or not...

Oh, and on the Email thing you can use COALESCE to create a comma-delimited string of all the entries in your email table, then use that as your To or...

I have a good one for you...

I have a table with 102 columns. The name of each column is an agency. The table has 59,000 rows. I have to look...

I implemented a Dynamic Properties Task at the start of the job to change all the destinations to the test server. It worked like a champ!!!