sql - 将行插入同一个表并存储旧/新标识列

Question

我正在尝试将表 A 中的行插入回表 A，同时维护新旧标识列。

这是我的基本示例:

t_course_media 
  course_media_id (PK, int, not null) -- identity column
  course_id (int, not null)
  media_id (int, null),
  ...

t_media 
  media_id (PK, int, not null), -- this is the identity column
  ...

我被要求将 3 门类(class)的类(class)媒体复制到一个现有的类(class)中。诀窍是现有类(class)将需要新的 media_id，以便每个类(class)都有唯一的 t_media 子行。我如何从插入中维护新的 media_id 以便我可以插入与我刚刚插入到 t_media 的新 t_media 行相关的正确 t_course_media 行？

到目前为止，我的研究使我找到了 MERGE 和 OUTPUT 语句。我发现的样本的问题是合并声明了一个新表。我可以让样本正常工作，但新的 media_id 值从 1 开始(而不是 t_media 表中的 xxxxx)。这是我找到的示例 - http://sqlblog.com/blogs/jamie_thomson/archive/2010/01/06/merge-and-output-the-swiss-army-knife-of-t-sql.aspx

DECLARE @source TABLE (
  [id] INT PRIMARY KEY,
  [name] VARCHAR(10)
);

INSERT @source VALUES(1000,'Harold'),(2000,'Madge');

DECLARE @destination TABLE (
  [id] INT PRIMARY KEY IDENTITY(1,1),
  NAME VARCHAR(10)
);

MERGE  @destination
USING  (SELECT [id], [name] FROM @source) AS [source]
ON     (1=0) --arbitrary join condition
WHEN   NOT MATCHED THEN
   INSERT (name)
   VALUES  (source.Name)
   OUTPUT  INSERTED.id AS NEWID,[source].[id] AS OldId,INSERTED.name; 

NewID    OldID    name
1        1000     Harold
2        2000     Madge

那么，当源表和目标表相同时，如何存储旧的和新的 t_media media_id？有数百行，最终我想创建一个程序来简化这个过程。

Answer 1

该解决方案假设表 是 设置为自动识别:

-- This solution assumes that all tables have
-- auto-identity on, as per your recent comment
BEGIN TRANSACTION insertNewCourse

    -- For capturing newly inserted course ID
    declare @newCourseID int 

    -- Insert new course
    INSERT INTO [dbo].[t_course]
               ([CourseInfo])  -- Example of other field
         VALUES
               ('extra field data')

    -- Capture new ID of course
    select  @newCourseID =  @@IDENTITY

    -- INSERT new data based on selection of 3 courses by their Course ID.
    INSERT INTO t_course_media
    SELECT  @newCourseID, media_id, SomeData
    FROM t_course_media
    WHERE course_id IN (2, 3, 4) -- IDs of existing 3 courses to copy


COMMIT transaction insertNewCourse