Does the table being inserted have triggers on it, or does it have foreign key constraints ?

Is your transaction log repeatedly auto-growing during the insert operation ?

Can't answer as to the difference between the @Table var and #Temp table - the exec plan will tell you. Possibly due to different statistics causing the optimizer to select...

You can get this error if a maintenance plan containing a re-index, or an auto-shrink

starts running concurrently with your stored proc.

You can eliminate this error by not using cursors.

>>Now I need to delete only those master records whose only relation was with the deleted category

In that scenario, you'll have a record in Master, with no remaining records...

Just wrap an update and insert in 1 transaction:

Begin Transaction

Update YourTable

Set  Column(s) = OtherTable.Columns

From YourTable

Inner Join OtherTable

  On (YourTable.KeyColumn(s) = OtherTable.KeyColumns(s))

Insert Into YourTable

Select ....

From OtherTable

Where Not Exists (

   Select *...

Checking the execution plans of each should show why there's a difference.

Are you thinking of a partitioned view ? In a partitioned view, the tables involved and UNION ALL'ed have...

Why does it have to be T-SQL ?

Use the right tool for the job. If you already have a DTS package, then use an ActiveX script task in the package...

Just use an empty string:

1 SQLCHAR 0 1 "" 1 LINE_TYPE SQL_Latin1_General_CP1_CI_AS

By aliasing the table in the FROM, but then updating the non-aliased tablename, you don't have correlation between the 2 objects:

-- Update the alias

update p

-- Don't use...

Is this really a "temp" table ? How persistent do you really need it to be ? Just for the lifespan of the stored procedure execution ? Or do you...

Why use DTS when you don't have to ?

This whole thing is triggered by calling a stored proc, right ?

So, use the BULK INSERT t-sql command within the stored proc...

Make sure logging is turned on for the package. In the ActiveX script task use this:

DTSPackageLog.WriteStringToLog "Your extra logging goes here"

... to add additional diagnostic traces to the DTS package...

>>Once these tasks complete I call a copy file package on the same server passing in the correct parameters.

How are the files being copied ?

Exec Process task calling a batch...

This is why you don't use float for monetary amounts:

Select cast(100.00 As float) As IHave100Bucks

Select cast(100.01 As float) As IDeposited1CentToMyAccount

You don't need to do that. If the file is not delimited and is in fixed width format, you use BCP in conjunction with a format file to specify the...