Mark,

Good catch. It seems these 3 all produce the same results. Must be due to the ordering of the data coming into the table and coincidence.

PARTITION BY #temp1.ProdID,...

After speculating about the timing with a larger record set, I got curious.

So I ran these two queries after creating/INSERTing into the two temp tables. They basicaly duplicate the...

Jeff - Thanks for pointing that out. I was a bit rushed (had to run off to a meeting) so I didn't carefully check the result set.

However the concept...

I believe this solution will also work and if you compare the actual query plan to the CROSS APPLY solution, it is about 5x better.

SELECT *

FROM (

SELECT #temp1.ProdID As...

I like this one:

SELECT DATEADD(hour, DATEDIFF(hour, 0, '2012-02-15 14:26:58.520'), 0)

Of course, if you want the PM part, you still need to convert.

John - True that multiple codes for a part_id did not show up in his data. And you are of course right that code may not be needed in...

John - You're way over my head with that.

Maybe grasshopper wants both fields in the join because a PART_ID can have more than one CODE?

If you want to join as you say, there's no need setting Code:

ALTER TRIGGER dbo.ORDERS_tg

ON dbo.ORDERS

AFTER INSERT,UPDATE

AS

BEGIN

SET NOCOUNT ON;

UPDATE o

SET SIZE =...

If I run this:

CREATE TABLE MASTERS

(PART_ID CHAR(6),

CODE CHAR(6),

SIZE NUMERIC(10,2),

ITEM_DESCRIPTION CHAR(50))

CREATE TABLE ORDERS

(ORDER_ID CHAR(6),

PART_ID CHAR(6),

QUANTITY NUMERIC(10,0),

CODE CHAR(6),

SIZE NUMERIC(10,2))

INSERT INTO MASTERS

VALUES ('1000','A100','10','TYPE A')

INSERT INTO MASTERS

VALUES ('1001','A101','12','TYPE A')

INSERT INTO MASTERS

VALUES ('1002','A200','20','TYPE B')

INSERT INTO...

Could be that o.PART_ID as the join criteria on MASTERS should be i.PART_ID.

Of course, it won't update anything if the PART_ID is not in MASTERS.

How about something like this:

CREATE TRIGGER dbo.ORDERS_tg

ON dbo.ORDERS

AFTER INSERT,UPDATE

AS

BEGIN

SET NOCOUNT ON;

UPDATE o

SET CODE = m.CODE

,SIZE = m.SIZE

FROM ORDERS o

INNER JOIN Inserted i

ON...