如何使用OUTPUT捕获新旧ID? [英] How to use OUTPUT to capture new and old ID?

问题描述

例如假设我有，

    create table product
    (
      ID INT IDENTITY(1,1) NOT NULL,
      Name VARCHAR(10)
    )

    insert into product(Name) values('a')
    insert into product(Name) values('b')
    insert into product(Name) values('c')
    insert into product(Name) values('d')
    insert into product(Name) values('e')

    create table #tempproduct
    (
      ID INT IDENTITY(1,1) NOT NULL,
      Name VARCHAR(10)
    )

    declare @OutputAttributesValues table
    (
      [NewID] INT, 
      [OldID] INT
    )

    insert into #tempproduct(Name)
    OUTPUT INSERTED.[ID],[ID] INTO @OutputAttributesValues
    select [Name] FROM product

请参阅我需要从输出中获取旧 ID 和新 ID.但是这个 sql 给了我错误无效的列名‘ID’."http://sqlfiddle.com/#!3/a27b2/1

See I need to get both Old and new ID from output. But this sql giving me error "Invalid column name 'ID'." http://sqlfiddle.com/#!3/a27b2/1

推荐答案

不能使用传统的 INSERT 输出非插入的字段.改用合并(警告:仅适用于数据库兼容性 >= 100):

You can't use traditional INSERT to output non-inserted fields. Use merge instead (caveat: only works with database compatibility >= 100):

MERGE
  #tempproduct AS t
USING(
  SELECT
      Name
      , ID
  FROM
    product) AS s
ON (1=0)  
WHEN NOT MATCHED   
  THEN INSERT (Name) VALUES (Name)
OUTPUT inserted.ID, s.ID INTO @OutputAttributesValues;

SELECT * FROM @OutputAttributesValues

这篇关于如何使用OUTPUT捕获新旧ID?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！