I just want the ItemID from the result of this EXCEPT statement:
我只是想要从这个结果中得到的ItemID:
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 new records which are the result of the same EXCEPT statement - out with the old in with the new.
基本上，我将根据ItemID的返回来删除记录，除了来自Products表的语句，然后插入新的记录，这是相同的结果，除了声明之外，旧的和新的。
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)
3 个解决方案
#1
2
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
在SQL Server 2008中，您可能希望对同步需求进行合并，但要回答这个问题，您可以这样做。
DECLARE @T TABLE (ItemID INT PRIMARY KEY)
;WITH T As
(
Your Big Statement
)
INSERT INTO @T
SELECT ItemID
FROM T
#2
1
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)
#3
1
Forget the CTE and table variable: just use MERGE.
忘记CTE和表变量:只使用MERGE。
The CTE is merely doing what the WHEN [NOT] MATCH part of MERGE does anyhow.
无论如何，CTE只是在做与合并部分相匹配的事情。
You already have a staging table (StagingProducts) so you don't need @T.
您已经有了一个staging表(StagingProducts)，所以您不需要@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);
#1
2
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
在SQL Server 2008中，您可能希望对同步需求进行合并，但要回答这个问题，您可以这样做。
DECLARE @T TABLE (ItemID INT PRIMARY KEY)
;WITH T As
(
Your Big Statement
)
INSERT INTO @T
SELECT ItemID
FROM T
#2
1
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)
#3
1
Forget the CTE and table variable: just use MERGE.
忘记CTE和表变量:只使用MERGE。
The CTE is merely doing what the WHEN [NOT] MATCH part of MERGE does anyhow.
无论如何，CTE只是在做与合并部分相匹配的事情。
You already have a staging table (StagingProducts) so you don't need @T.
您已经有了一个staging表(StagingProducts)，所以您不需要@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);