Data import "upsert" from a SSIS package I have the following "upsert" trigger in SQL Server 2005, which is handling a large data import from a SSIS package. It is taking a very long time to complete - I suspect due to the concatenation and CASTing of keys. 

Is there a better, faster way of doing this?

CREATE TRIGGER [dbo].[MP_VOD_UPSERT] 
ON  [dbo].[vwMP_VOD] 
INSTEAD OF INSERT
AS 
BEGIN

  SET NOCOUNT ON;

  UPDATE [dbo].[vwMP_VOD]
  SET 
  [Org] = inserted.Org
  ,[id_vend] = inserted.id_vend
  ,[id_org_orig] = inserted.id_org_orig
  ,[id_vchr_nbr] = inserted.id_vchr_nbr
  ,[id_vchr_line] = inserted.id_vchr_line
  ,[id_vchr_line_dist] = inserted.id_vchr_line_dist
  ,[id_glac_exp] = inserted.id_glac_exp
  ,[text_ref_4] = inserted.text_ref_4
  ,[amt_exp_hcur] = inserted.amt_exp_hcur
  ,[amt_rem_hcur] = inserted.amt_rem_hcur
  ,[id_tax] = inserted.id_tax
  ,[code_type_chrg] = inserted.code_type_chrg
  ,[id_vchr_line_chrg] = inserted.id_vchr_line_chrg
  FROM inserted 
  WHERE inserted.ORG + inserted.ID_VEND + inserted.ID_ORG_ORIG + CAST(inserted.ID_VCHR_NBR AS VARCHAR) + CAST(inserted.ID_VCHR_LINE AS VARCHAR) + CAST(inserted.ID_VCHR_LINE_DIST AS VARCHAR) + inserted.CODE_TYPE_CHRG + inserted.ID_VCHR_LINE_CHRG IN
  (
    SELECT ORG + ID_VEND + ID_ORG_ORIG + CAST(ID_VCHR_NBR AS VARCHAR) + CAST(ID_VCHR_LINE AS VARCHAR) + CAST(ID_VCHR_LINE_DIST AS VARCHAR) + CODE_TYPE_CHRG + ID_VCHR_LINE_CHRG 
    FROM inserted 
    INTERSECT 
    SELECT ORG + ID_VEND + ID_ORG_ORIG + CAST(ID_VCHR_NBR AS VARCHAR) + CAST(ID_VCHR_LINE AS VARCHAR) + CAST(ID_VCHR_LINE_DIST AS VARCHAR) + CODE_TYPE_CHRG + ID_VCHR_LINE_CHRG 
    FROM [dbo].[vwMP_VOD]
  ) 


  INSERT INTO [dbo].[vwMP_VOD] 
  SELECT * FROM inserted 
  WHERE ORG + ID_VEND + ID_ORG_ORIG + CAST(ID_VCHR_NBR AS VARCHAR) + CAST(ID_VCHR_LINE AS VARCHAR) + CAST(ID_VCHR_LINE_DIST AS VARCHAR) + CODE_TYPE_CHRG + ID_VCHR_LINE_CHRG IN
  (
    SELECT ORG + ID_VEND + ID_ORG_ORIG + CAST(ID_VCHR_NBR AS VARCHAR) + CAST(ID_VCHR_LINE AS VARCHAR) + CAST(ID_VCHR_LINE_DIST AS VARCHAR) + CODE_TYPE_CHRG + ID_VCHR_LINE_CHRG 
    FROM inserted 
    EXCEPT 
    SELECT ORG + ID_VEND + ID_ORG_ORIG + CAST(ID_VCHR_NBR AS VARCHAR) + CAST(ID_VCHR_LINE AS VARCHAR) + CAST(ID_VCHR_LINE_DIST AS VARCHAR) + CODE_TYPE_CHRG + ID_VCHR_LINE_CHRG 
    FROM [dbo].[vwMP_VOD]
  )

END
I can pretty much guarantee it's the concatenation that's the problem.  Use the columns as columns; you don't want to handcuff the optimizer when it puts together your query plan.  Here's a pretty typical "upsert" pattern:

  UPDATE [dbo].[vwMP_VOD]
  SET [Org] = inserted.Org
  ,[id_vend] = inserted.id_vend
  ,[id_org_orig] = inserted.id_org_orig
  ,[id_vchr_nbr] = inserted.id_vchr_nbr
  ,[id_vchr_line] = inserted.id_vchr_line
  ,[id_vchr_line_dist] = inserted.id_vchr_line_dist
  ,[id_glac_exp] = inserted.id_glac_exp
  ,[text_ref_4] = inserted.text_ref_4
  ,[amt_exp_hcur] = inserted.amt_exp_hcur
  ,[amt_rem_hcur] = inserted.amt_rem_hcur
  ,[id_tax] = inserted.id_tax
  ,[code_type_chrg] = inserted.code_type_chrg
  ,[id_vchr_line_chrg] = inserted.id_vchr_line_chrg
  FROM inserted
      INNER JOIN (
         SELECT ORG, ID_VEND, ID_ORG_ORIG, ID_VCHR_NBR, ID_VCHR_LINE, ID_VCHR_LINE_DIST, CODE_TYPE_CHRG, ID_VCHR_LINE_CHRG 
         FROM [dbo].[vwMP_VOD]
    ) AS V 
      ON inserted.Org = V.Org 
        AND inserted.ID_VEND = V.ID_VEND
        AND inserted.ID_ORG_ORIG = V.ID_ORG_ORIG
        AND inserted.ID_VCHR_NBR = V.ID_VCHR_NBR
        AND inserted.ID_VCHR_LINE = V.ID_VCHR_LINE
        AND inserted.ID_VCHR_LINE_DIST = V.ID_VCHR_LINE_DIST
        AND inserted.CODE_TYPE_CHRG = V.CODE_TYPE_CHRG
        AND inserted.ID_VCHR_LINE_CHRG = V.ID_VCHR_LINE_CHRG;

INSERT INTO [dbo].[vwMP_VOD] 
SELECT inserted.* 
FROM inserted 
    LEFT JOIN (
         SELECT ORG, ID_VEND, ID_ORG_ORIG, ID_VCHR_NBR, ID_VCHR_LINE, ID_VCHR_LINE_DIST, CODE_TYPE_CHRG, ID_VCHR_LINE_CHRG 
         FROM [dbo].[vwMP_VOD]
    ) AS V 
      ON inserted.Org = V.Org 
        AND inserted.ID_VEND = V.ID_VEND
        AND inserted.ID_ORG_ORIG = V.ID_ORG_ORIG
        AND inserted.ID_VCHR_NBR = V.ID_VCHR_NBR
        AND inserted.ID_VCHR_LINE = V.ID_VCHR_LINE
        AND inserted.ID_VCHR_LINE_DIST = V.ID_VCHR_LINE_DIST
        AND inserted.CODE_TYPE_CHRG = V.CODE_TYPE_CHRG
        AND inserted.ID_VCHR_LINE_CHRG = V.ID_VCHR_LINE_CHRG
WHERE V.Org IS NULL;


What I've done here is to take the your WHERE IN condition and replicate it with a join instead.  

In the "update" case, you wanted all rows in the INSERTED table that are also in the [dbo].[vwMP_VOD] table.  Textbook inner join.  Joining on the key columns should be pretty quick.  If not, make sure your table has a PRIMARY KEY defined on these columns; if you have a surrogate key (ID column) instead, then you can still add a UNIQUE INDEX on the columns used to identify the rows in this upsert.

In the "insert" case, you want all rows in the INSERTED table that are not in [dbo].[vwMP_VOD].  To avoid concatenating all the key columns together, I simulated the effects of the EXCEPT operation by using a LEFT JOIN from INSERTED to [dbo].[vwMP_VOD], joining on all key columns, and adding a WHERE condition that checks that a non-nullable column from [dbo].[vwMP_VOD] is null.  That reliably indicates that there was no row present in the right-hand table ([dbo].[vwMP_VOD]).