How to retrieve lookup table of inserted & original IDs

Transact-SQL
1

I've run into a catch-22 situation with this SQL. I'm trying to insert a number of new rows, which are copies of existing rows in the same table. I want to return a lookup table that contains 2 columns: a) the newly inserted identity value, and the identity value of the row it was copied from. My SQL is this:

DECLARE @MyTableVar TABLE (
  NewID INT,
  OldID INT
)

INSERT INTO Clients (FirstName, LastName)
OUTPUT INSERTED.ClientID, c.ClientID INTO @MyTableVar
SELECT c.FirstName, c.LastName FROM Clients c WHERE c.IsGoodClient = 1;

SELECT * FROM @MyTableVar;

The problem of course is that it can't find "c.ClientID" in the source row, because it's not in the SELECT statement. However if I do include c.ClientID in the SELECT statement, then of course the number of columns doesn't match with the INSERT.

It's probably evident what I'm trying to achieve here... to return a lookup table matching the new IDs with the original IDs. How is that normally done? I'm using MS SQL Server Express 2012. Thanks!

2

It's not a solution that I like but it's the only thing that's coming to mind at the moment...

You could temporarily ad an 'OldID' column to the Clients table and then immediately drop the column following the insert.

IF OBJECT_ID('tempdb..#Clients', 'U') IS NOT NULL 
DROP TABLE #Clients;

CREATE TABLE #Clients (
	ClientID INT NOT NULL PRIMARY KEY IDENTITY(1,1),
	FirstName VARCHAR(20) NOT NULL,
	LastName VARCHAR(20) NOT NULL,
	IsGoodClient BIT NOT NULL DEFAULT (0)
	);
INSERT #Clients (FirstName, LastName, IsGoodClient) VALUES
	('Joe', 'Blow', 0), ('John', 'Doe', 1), ('Jane', 'Doe', 1),
	('Mark', 'Jones', 0), ('Steve', 'Smith', 1);

-- SELECT * FROM #Clients c;

--=============================================

ALTER TABLE #Clients ADD OldID INT; 
GO 

DECLARE @MyTableVar TABLE (
  NewID INT,
  OldID INT
	);

INSERT #Clients (FirstName, LastName, OldID)
OUTPUT Inserted.ClientID, Inserted.OldID INTO @MyTableVar ( NewID, OldID )
SELECT 
	c.FirstName,
	c.LastName,
	c.ClientID
FROM 
	#Clients c
WHERE 
	c.IsGoodClient = 1;

ALTER TABLE #Clients DROP COLUMN OldID;

SELECT * FROM @MyTableVar mtv;
SELECT * FROM #Clients c
1 Like
3

Thanks Jason, it certainly makes sense as a one-off, but will need to be done on a regular basis as part of normal use of a web app. So if nothing else works, I'll probably go with adding a column like that permanently for this use case. Is such a column a commonly used technique for this sort of thing? Surely it must come up a lot with relational dbs if certain info needs to be "cloned", but it's been difficult finding any examples!

4

Reinserting duplicate value into the original table is odd all by itself... I'm having a difficult time coming up with a reason why you're doing this in the 1st place.

Assuming there is a legit reason for doing this, I'd want something to let me know that one is active and one is inactive. In that case I'd say it's perfectly acceptable to have a column that serves that purpose.

5

It's a survey application, each new survey is "cloned" from a template survey (or another previous survey) then customised. So for example each survey has a number of questions, in a Questions table - those rows belonging to the template survey need to be copied (within the same table) and attached to the new survey, There's more, it's complicated. :slight_smile: I didn't want to write a big explanation which might have confused things, so used a simple example.

6

Seems like a proper schema design wouldn't require that. Why not set it up as a proper many to many relationship, where each client has 1 and only 1 ClientID that can participate in many surveys?

You have to know that creating a "client copy", with a new ClientID, for every new survey is going to be a recipe pain not too far down the road.

Cloning and existing survey as a shortcut for creating new surveys, makes perfect sense. cloning the participants the surveys doesn't

Just my 2 cents...

1 Like
7

Thanks Jason, but please don't infer anything about the system as a whole from the "client" example I gave, it was only to illustrate what I wanted to do, without getting into too much detail. It's not that way in the product. Yes, there is a global table of participants (unique individual identities), and yes there's a client table with a one-to-many r/ship with surveys. It's really too much to get into here, so I gave a simple example. I didn't mean to imply anything about design from the example.

I avoided using a "books and authors" example, as then I knew people would try to convince me that duplicating book data wasn't good design. :slight_smile: Probably should have given a real-world example but I thought keeping it simple would be better. :slight_smile:

8

It's tough to not make inferences but I'll take your word for it.

9

I'd consider exploring the creation of a 'template' survey. It seems the table is serving two functions. Perhaps some kind of interface such as a UDF or view might be appropriate. That's from a design/development perspective.

10

Ok this is the solution I worked out. I've written the whole 'proof' here so you can run it and see how it works. The result is what I was after - a resultset which maps the original row IDs I want to copy to the newly created row IDs.

Forget the "clients" example above, it seems to be confusing the issue. This example shows how a survey, along with its related questions, is copied to a new survey, and the resultset contains the new survey ID along with a map between the original questions IDs and the new question IDs. That mapping of IDs is what I was after.

DECLARE @Surveys TABLE
(
    SurveyId INT IDENTITY PRIMARY KEY,
    SurveyTitle VARCHAR(100) NOT NULL,
    IsTemplate TINYINT NOT NULL DEFAULT 0
)

DECLARE @Questions TABLE
(
    QuestionId INT IDENTITY PRIMARY KEY,
    SurveyId INT NOT NULL,
    QuestionNumber SMALLINT NOT NULL,
    QuestionText VARCHAR(1000) NOT NULL
)

-- Couple of template surveys. IDs will be 1 and 2
INSERT INTO @Surveys (SurveyTitle, IsTemplate)
  VALUES 
  ('Survey 1', 1),
  ('Survey 2', 1);

-- Questions for survey 1. IDs will be 1 and 2
INSERT INTO @Questions (SurveyId, QuestionNumber, QuestionText)
  VALUES 
  (1, 1, 'Question 1'),
  (1, 2, 'Question 2');
-- Questions for survey 2. IDs will be 3 and 4
INSERT INTO @Questions (SurveyId, QuestionNumber, QuestionText)
  VALUES 
  (2, 1, 'Another Question 1'),
  (2, 2, 'Another Question 2');

-- Clone survey 1 and get the new survey ID
DECLARE @CopySurveyId INT = 1;
DECLARE @TempTable TABLE (Id INT);
DECLARE @NewSurveyId INT;
INSERT INTO @Surveys (SurveyTitle, IsTemplate)
  OUTPUT INSERTED.SurveyId INTO @TempTable
  SELECT SurveyTitle, 0 FROM @Surveys WHERE SurveyId = @CopySurveyId;
SELECT @NewSurveyId = Id FROM @TempTable; -- Put new SurveyId into scalar variable.

-- This is what I was looking for:
-- Clone questions from template into the new survey
-- and get a "lookup table" mapping the original question IDs to the new question IDs.
DECLARE @QuestionMap TABLE (NewSurveyId INT, TemplateQuestionId INT, NewQuestionId INT);
MERGE @Questions USING (
  SELECT QuestionId, SurveyId, QuestionNumber, QuestionText
    FROM @Questions
   WHERE SurveyId = @CopySurveyId
) q ON 0 = 1 -- ensure always not matched
WHEN NOT MATCHED
THEN INSERT (SurveyId, QuestionNumber, QuestionText)
  VALUES (@NewSurveyId, q.QuestionNumber, q.QuestionText)
  OUTPUT @NewSurveyId, q.QuestionId, INSERTED.QuestionId INTO @QuestionMap; -- original ID and new ID

SELECT * FROM @QuestionMap -- yay!

The resultset from the above is this:

NewSurveyID    TemplateQuestionId    NewQuestionId
     3                  1                  5
     3                  2                  6

With thanks to this post on SO for suggesting the MERGE solution: https://stackoverflow.com/a/8774519/471597