A SQL trigger that contains a cursor, where the cursor is based on a SELECT statement that has a sub-SELECT within the main SELECT is a recipe for performance disaster.

This...

>>so I'm guessing

Generally not a good recipe for a succesful implementation

13097 is not a date. You need to find out the business...

>>many date fields, all set to datatype smallint with numbers like '13097'

That's not really enough info to work with.

What does 13097 represent ? Is it the number of days (or...

The "format" of a datetime column is simply a presentation issue. It is just an 8 byte number behind the scenes.

It is usually better to model data in a way...

Killing 2 birds with the 1 stone, first off the NULL issue, then the looming performance issue by using inefficient and unnecessary counts:

IF EXISTS (

  SELECT *

  FROM...

It has nothing to do with the primary key.

It is a syntax error on the INSERT statements. Try this:

INSERT INTO TABLEA (X, Y)

VALUES(0, 01/02/2003);

SELECT * FROM TABLEA

Notice anything ? What...

This started with SP4. I've never seen it called a "bug".

The best explanation I've seen so far is here:

http://groups.google.com/group/microsoft.public.sqlserver.server/msg/b86e343e513ab281?hl=en

>>They are all non-clustered Indexes.

Do you have a clustered index on this 1 large table ? If not, you may have developed a highly fragmented heap.

What type of database is it ? What is the nature of the application(s) it supports ? OLTP ? Reporting ? Datamart ?

>> so when I try to run this, I get a primary key error

The only way that could happen with the code I posted is if there are more than...

You typically do this by joining to a derived table that generates the Max() of each group for joining to. In your case, you need a derived table of Max(last_updated)...

-- Create a temp table to re-sequence

Create Table #ReSequence

(

  stulink int,

  [date] smalldatetime,

  Sequence Int Identity

)

-- Insert in stulink/date otder. Table is now sequenced.

-- But each stulink does not...

[Edit] Ugh. I read the question all wrong, original reply removed.

Just be sure to read this if ordering of rows is involved: 

http://support.microsoft.com/default.aspx?scid=kb;en-us;273586

You can't truncate a table that has a foreign key referencing it, even if the table or tables doing the referencing are empty.

You'll have to temporarily drop/disable the foreign keys.

You could have 1 exec SQL task, doing the Select Count(*) into an output parameter, mapped to a DTS package variable.

Then a 2nd exec SQL task for the insert, where...