CODEWITHSUNDEEP
sqlsql-server-2008t-sql
stevewaugh76
stevewaugh76

Reputation: 45

Insert Rows Into Same Table and Store Old/New Identity Column

I'm trying to insert rows from Table A back into Table A while maintaining the old and new identity column.

Here's 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've been asked to copy course media from 3 courses into a single existing course. The trick is the existing course will need the new media_id so that each course has unique t_media child rows. How do I maintain the new media_id from the insert so that I can insert the proper t_course_media rows related to the new t_media rows that I just inserted into t_media?

So far my research has led me to the MERGE and OUTPUT statements. The problem with the samples I've found is the merge is declaring a new table. I can get the sample to work fine but the new media_id values start at 1 (as opposed to xxxxx from the t_media table). Here's the example I found - 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

So how do I go about storing the old and new t_media media_id when the source and destination tables are the same? There are hundreds of rows and ultimately I'd like to create a procedure to simplify the process.

Upvotes: 4

Views: 2295

Answers (2)

CJBS
CJBS

Reputation: 15685

This solution works assuming that the tables are set to 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

Upvotes: 2

CJBS
CJBS

Reputation: 15685

This solution works assuming that the tables are NOT set to 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

Upvotes: 0

Related Questions