Thanks for the tip Grasshopper, messing with SET IDENTITY_INSERT is yet even faster

Do you mean insert the ids into the new table directly from the old table? If so, this won't work because those ids are used already.

Or do you mean...

Well I got it down to just over 2 minutes by inserting a column into the new table to store the old id, then using that after the bulk insert...

In some cases multiple records have duplicate data (even timestamp) so I couldn't figure out how to match the new inserted recs back to the old table.

I will put some...

Thanks to all for input, especially Daryl for a solution that works

Using the text comparison seemed to work fine, thanks!

Adding the alias fixed the previous error, but now I get this error:

"Conversion failed when converting the varchar value 'ALL' to data type int."

But hosp_id = 'ALL' was properly filtered...

It looks like Ian's solution should work for my data, but why am I getting a syntax error? Incorrect syntax near ')'

select count(Wt_Id) as Weight_Unit

from (select Wt_Id, Hosp_id...

Looks promising, thanks! ... will do more testing...

I am guessing that dropping the trigger on production will not be an option but I'll have to see what my mgr says.

I did verify that the whole mess works...

Oh duh, that solves part of the mystery. But ugh, i really don't think we want to mess with the triggers since who knows what side effects that may...

I haven't used profiler before but no time like the present, i'll figure it out.

Interestingly, I can update the UpdatedBy values by issuing single UPDATE stmts, and when...

Also, I wrote the code to determine the next AgeId before I realized that there was a trigger that would do it. So my SQL to insert can be...

Hmmm, weird. I am going to go back and try to figure out why i can't update the UpdatedBy column. This db was created by consultants who are...

Here is my SQL to do the insert-

DECLARE @maxNum int

DECLARE @increment smallint

DECLARE @IdsAgeUnit table (idOld int, idNew int)

SELECT@maxNum = max_num, @increment = increment

FROM StefHpl_prod.hpl.Table_List WHERE Table_Name = 'Age_Unit'

INSERT...