Geek asks and answers

Insert rows into one table and save the old / new identity column

I am trying to insert rows from table A back into table A, keeping the old and new columns.

Here is my basic example:

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
  ...

I was asked to copy course materials from 3 courses to one existing course. The trick in the existing course will require a new media_id so that each course has unique t_media child strings. How to save the new media_id from the insert so that I can insert the correct t_course_media lines associated with the new t_media lines that I just inserted into t_media?

MERGE OUTPUT. , , , . , media_id 1 ( xxxxx t_media). , - 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, ? , .

+3
2

, , auto-ident:

-- 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
0

, , auto-ident:

-- This solution assumes that none of these tables have 
-- auto-identity on, as per your example
BEGIN TRANSACTION insertNewCourse

    -- Capture max course ID
    declare @newCourseID int 
    select  @newCourseID = (max(course_id) + 1) from t_course

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

    -- Capture max course_media_id
    declare @maxCourseMediaID int 
    select  @maxCourseMediaID = max(course_media_id) from t_course_media;

    -- Use CTE to get existing data on 3 courses
    WITH MatchingCourses_CTE (RowNum, course_id, media_id, SomeData)
    AS
    -- Common Table Expression (CTE) query.
    (
        select ROW_NUMBER()
        OVER (ORDER BY t_course_media.course_id) , course_id ,  media_id,    SomeData -- example additional data field
        from t_course_media     
    )
    -- INSERT new data based on that selected from CTE
    INSERT INTO t_course_media
    SELECT  RowNum + @maxCourseMediaID, -- Ensure that non-auto-ident PK doesn't conflict
            @newCourseID, media_id, SomeData
    FROM MatchingCourses_CTE
    WHERE course_id IN (2, 3, 4) -- IDs of existing 3 courses to copy

COMMIT transaction insertNewCourse
0

More articles:


All Articles