CREATE TABLE dbo.Target (
   TargetId int identity(1,1) NOT NULL,Color varchar(20) NOT NULL,Action varchar(10) NOT NULL,-- of course this should be normalized
   Code int NOT NULL,CONSTRAINT PK_Target PRIMARY KEY CLUSTERED (TargetId)
);

在两个略有不同的场景中,我想插入行并返回标识列中的值.

情景1

INSERT dbo.Target (Color,Action,Code)
OUTPUT inserted.TargetId
SELECT t.Color,t.Action,t.Code
FROM
   (VALUES
      ('Blue','New',1234),('Blue','Cancel',4567),('Red',5678)
   ) t (Color,Code)
;

场景2

CREATE TABLE #Target (
   Color varchar(20) NOT NULL,Code int NOT NULL,PRIMARY KEY CLUSTERED (Color,Action)
);

-- Bulk insert to the table the same three rows as above by any means

INSERT dbo.Target (Color,t.Code
FROM #Target
;

题

我可以依赖于dbo.Target表插入中返回的标识值,按照它们在1)VALUES子句和2)#Target表中存在的顺序返回,这样我就可以根据它们在输出行集中的位置来关联它们到原始输入？

以供参考

下面是一些精简的C#代码,用于演示应用程序中发生的情况(方案1,很快将转换为使用sqlBulkCopy)：

public IReadOnlyCollection<Target> InsertTargets(IEnumerable<Target> targets) {
   var targetList = targets.ToList();
   const string insertsql = @"
      INSERT dbo.Target (
         CoreItemId,TargetDateTimeUtc,TargetTypeId,)
      OUTPUT
         Inserted.TargetId
      SELECT
         input.CoreItemId,input.TargetDateTimeUtc,input.TargetTypeId,FROM
         (VALUES
            {0}
         ) input (
            CoreItemId,TargetTypeId
         );";
   var results = Connection.Query<DbTargetInsertResult>(
      string.Format(
         insertsql,string.Join(
            ",",targetList
               .Select(target => $@"({target.CoreItemId
                  },'{target.TargetDateTimeUtc:yyyy-MM-ddTHH:mm:ss.fff
                  }',{(byte) target.TargetType
                  })";
               )
         )
      )
      .ToList();
   return targetList
      .Zip( // The correlation that relies on the order of the two inputs being the same
         results,(inputTarget,insertResult) => new Target(
            insertResult.TargetId,// with the new TargetId to replace null.
            inputTarget.TargetDateTimeUtc,inputTarget.CoreItemId,inputTarget.TargetType
         )
      )
      .ToList()
      .AsReadOnly();
}