select single column from EXCEPT result

I just want the ItemID from the result of this EXCEPT statement:

SELECT     ManufacturerID, ItemID, ItemName, Description, Notes, Dimensions, BasePrice, SpecialPrice, OrderMinimumQuantity, OrderMultipleQuantity, 
                      OnHandQuantity, Category, IntroDate, BackOrderDate, UPC, PriceLevel1, PriceLevel2, PriceLevel3, PriceLevel4, PriceLevel5, PriceLevel6, PriceLevel7, PriceLevel8, 
                      PriceLevel9, PieceBox, Cubes, UnitOfMeasure, UDF1, UDF2, UDF3, UDF4, UDF5, AdditionalImageCount, PhotoName, AppendProductModifiers, Discontinued, 
                      IsDeleted
FROM         StagingProducts
WHERE     (ManufacturerID = 10)
EXCEPT 
SELECT     ManufacturerID, ItemID, ItemName, Description, Notes, Dimensions, BasePrice, SpecialPrice, OrderMinimumQuantity, OrderMultipleQuantity, 
                      OnHandQuantity, Category, IntroDate, BackOrderDate, UPC, PriceLevel1, PriceLevel2, PriceLevel3, PriceLevel4, PriceLevel5, PriceLevel6, PriceLevel7, PriceLevel8, 
                      PriceLevel9, PieceBox, Cubes, UnitOfMeasure, UDF1, UDF2, UDF3, UDF4, UDF5, AdditionalImageCount, PhotoName, AppendProductModifiers, Discontinued, 
                      IsDeleted
FROM         Products
WHERE     (ManufacturerID = 10)

What would be really nice is if I could save the results of the EXCEPT statement for use in a INSERT INTO query further down the line.

Basically I am going to delete records based on the ItemID's return from this EXCEPT statement from the Products table then insert the ne开发者_JS百科w records which are the result of the same EXCEPT statement - out with the old in with the new.

UPDATE - working solution:

    DECLARE @T TABLE (
    [ManufacturerID] [int] NOT NULL,
    [ItemID] [nvarchar](50) NULL,
    [ItemName] [nvarchar](100) NOT NULL,
    [Description] [nvarchar](max) NULL,
    [Notes] [nvarchar](200) NULL,
    [Dimensions] [nvarchar](50) NULL,
    [BasePrice] [money] NOT NULL,
    [SpecialPrice] [money] NULL,
    [OrderMinimumQuantity] [int] NOT NULL,
    [OrderMultipleQuantity] [int] NOT NULL,
    [OnHandQuantity] [int] NULL,
    [Category] [nvarchar](100) NULL,
    [IntroDate] [date] NULL,
    [BackOrderDate] [date] NULL,
    [UPC] [nvarchar](25) NULL,
    [PriceLevel1] [decimal](18, 0) NULL,
    [PriceLevel2] [decimal](18, 0) NULL,
    [PriceLevel3] [decimal](18, 0) NULL,
    [PriceLevel4] [decimal](18, 0) NULL,
    [PriceLevel5] [decimal](18, 0) NULL,
    [PriceLevel6] [decimal](18, 0) NULL,
    [PriceLevel7] [decimal](18, 0) NULL,
    [PriceLevel8] [decimal](18, 0) NULL,
    [PriceLevel9] [decimal](18, 0) NULL,
    [PieceBox] [int] NULL,
    [Cubes] [decimal](18, 0) NULL,
    [UnitOfMeasure] [nvarchar](10) NULL,
    [UDF1] [nvarchar](50) NULL,
    [UDF2] [nvarchar](50) NULL,
    [UDF3] [nvarchar](50) NULL,
    [UDF4] [nvarchar](50) NULL,
    [UDF5] [nvarchar](50) NULL,
    [AdditionalImageCount] [smallint] NULL,
    [PhotoName] [nvarchar](50) NULL,
    [AppendProductModifiers] [bit] NULL,
    [Discontinued] [bit] NULL,
    [IsDeleted] [bit] NOT NULL)

;WITH T As
(SELECT     ManufacturerID, ItemID, ItemName, Description, Notes, Dimensions, BasePrice, SpecialPrice, OrderMinimumQuantity, OrderMultipleQuantity, 
                      OnHandQuantity, Category, IntroDate, BackOrderDate, UPC, PriceLevel1, PriceLevel2, PriceLevel3, PriceLevel4, PriceLevel5, PriceLevel6, PriceLevel7, PriceLevel8, 
                      PriceLevel9, PieceBox, Cubes, UnitOfMeasure, UDF1, UDF2, UDF3, UDF4, UDF5, AdditionalImageCount, PhotoName, AppendProductModifiers, Discontinued, 
                      IsDeleted
FROM         StagingProducts
WHERE     (ManufacturerID = @ManufacturerID)
EXCEPT 
SELECT     ManufacturerID, ItemID, ItemName, Description, Notes, Dimensions, BasePrice, SpecialPrice, OrderMinimumQuantity, OrderMultipleQuantity, 
                      OnHandQuantity, Category, IntroDate, BackOrderDate, UPC, PriceLevel1, PriceLevel2, PriceLevel3, PriceLevel4, PriceLevel5, PriceLevel6, PriceLevel7, PriceLevel8, 
                      PriceLevel9, PieceBox, Cubes, UnitOfMeasure, UDF1, UDF2, UDF3, UDF4, UDF5, AdditionalImageCount, PhotoName, AppendProductModifiers, Discontinued, 
                      IsDeleted
FROM         Products
WHERE     (ManufacturerID = @ManufacturerID)
)
INSERT INTO  @T
SELECT * 
FROM T

    -- Kill the old products
    Delete FROM Products where ManufacturerID = @ManufacturerID
        AND ItemID IN(SELECT ItemID FROM @T)

    -- insert the new products  
    INSERT INTO Products ([ManufacturerID]
           ,[ItemID]
           ,[ItemName]
           ,[Description]
           ,[Notes]
           ,[Dimensions]
           ,[BasePrice]
           ,[SpecialPrice]
           ,[OrderMinimumQuantity]
           ,[OrderMultipleQuantity]
           ,[OnHandQuantity]
           ,[Category]
           ,[IntroDate]
           ,[BackOrderDate]
           ,[UPC]
           ,[PriceLevel1]
           ,[PriceLevel2]
           ,[PriceLevel3]
           ,[PriceLevel4]
           ,[PriceLevel5]
           ,[PriceLevel6]
           ,[PriceLevel7]
           ,[PriceLevel8]
           ,[PriceLevel9]
           ,[PieceBox]
           ,[Cubes]
           ,[UnitOfMeasure]
           ,[UDF1]
           ,[UDF2]
           ,[UDF3]
           ,[UDF4]
           ,[UDF5]
           ,[AdditionalImageCount]
           ,[PhotoName]
           ,[AppendProductModifiers]
           ,[Discontinued]
           ,[CreatedOn]
           ,[CreatedBy]
           ,[ModifiedOn]
           ,[ModifiedBy]
           ,[DeletedOn]
           ,[DeletedBy]
           ,[IsDeleted])
    SELECT [ManufacturerID]
      ,[ItemID]
      ,[ItemName]
      ,[Description]
      ,[Notes]
      ,[Dimensions]
      ,[BasePrice]
      ,[SpecialPrice]
      ,[OrderMinimumQuantity]
      ,[OrderMultipleQuantity]
      ,[OnHandQuantity]
      ,[Category]
      ,[IntroDate]
      ,[BackOrderDate]
      ,[UPC]
      ,[PriceLevel1]
      ,[PriceLevel2]
      ,[PriceLevel3]
      ,[PriceLevel4]
      ,[PriceLevel5]
      ,[PriceLevel6]
      ,[PriceLevel7]
      ,[PriceLevel8]
      ,[PriceLevel9]
      ,[PieceBox]
      ,[Cubes]
      ,[UnitOfMeasure]
      ,[UDF1]
      ,[UDF2]
      ,[UDF3]
      ,[UDF4]
      ,[UDF5]
      ,[AdditionalImageCount]
      ,[PhotoName]
      ,[AppendProductModifiers]
      ,[Discontinued]
      ,[CreatedOn]
      ,[CreatedBy]
      ,[ModifiedOn]
      ,[ModifiedBy]
      ,[DeletedOn]
      ,[DeletedBy]
      ,[IsDeleted] from StagingProducts
      Where ManufacturerID = @ManufacturerID
      AND ItemID IN(SELECT ItemID FROM @T)

As you are on SQL Server 2008 you might want to look into MERGE for your synchronisation needs but to answer the question asked you can do

DECLARE @T TABLE (ItemID INT PRIMARY KEY)

;WITH T As
(
Your Big Statement
)
INSERT INTO  @T
SELECT ItemID 
FROM T

Separate to Martin's answer about using MERGE...

You can use NOT EXISTS which gives the same query plan and is more readable.

SELECT     ManufacturerID, ItemID, ItemName, Description, Notes, Dimensions, BasePrice, SpecialPrice, OrderMinimumQuantity, OrderMultipleQuantity, 
                      OnHandQuantity, Category, IntroDate, BackOrderDate, UPC, PriceLevel1, PriceLevel2, PriceLevel3, PriceLevel4, PriceLevel5, PriceLevel6, PriceLevel7, PriceLevel8, 
                      PriceLevel9, PieceBox, Cubes, UnitOfMeasure, UDF1, UDF2, UDF3, UDF4, UDF5, AdditionalImageCount, PhotoName, AppendProductModifiers, Discontinued, 
                      IsDeleted
FROM         StagingProducts SG
WHERE
     (ManufacturerID = @ManufacturerID)

NOT EXISTS (SELECT * FROM
         Products P
       WHERE
          P.Key1 = SG.Key1 AND SG.Key2 = SG.Key2 AND SG.Key3 = SG.Key3)

Forget the CTE and table variable: just use MERGE.

The CTE is merely doing what the WHEN [NOT] MATCH part of MERGE does anyhow.

You already have a staging table (StagingProducts) so you don't need @T.

The MERGE will look something like this (I've shortened the column list with ...):

MERGE INTO Products
USING StagingProducts
   ON Products.ManufacturerID = StagingProducts.ManufacturerID
      AND Products.ItemID = StagingProducts.ItemID
      AND Products.ManufacturerID = @ManufacturerID
WHEN MATCHED THEN
   UPDATE 
      SET ItemName = StagingProducts.ItemName, 
          Description = StagingProducts.Description, 
          Notes = StagingProducts.Notes, 
          ...
          IsDeleted = StagingProducts.IsDeleted 
WHEN NOT MATCHED THEN
   INSERT (ManufacturerID, ItemID, ItemName, Description, Notes, ..., IsDeleted)
      VALUES (ManufacturerID, ItemID, ItemName, Description, Notes, ..., IsDeleted);