October 9, 2008 at 10:10 am
Is there any way to have the OUTPUT clause of an INSERT statement return a column from the source table of the INSERT when that data is not part of the table being inserted into?
I need to be able correlate the newly-inserted TestID identity with SourceID. Source ID is not stored in #Test. In a subsequent operation I need to be able to insert TestID and SourceID into a different table.
Here's an example of what I would like to be able to do, which obviously does not work:
CREATE TABLE #Source (SourceID int IDENTITY, SomeData varchar(80))
INSERT INTO #Source (SomeData) VALUES ('ABC')
INSERT INTO #Source (SomeData) VALUES ('DEF')
CREATE TABLE #Test (TestID int IDENTITY, MyData varchar(80))
INSERT INTO #Test (MyData)
OUTPUT INSERTED.TestID, src.SourceID
SELECT SomeData FROM #Source src
The work-around is of course to go ahead and add the SourceID column to #Test...but the only reason it would be there is to support the OUTPUT clause. This is not very elegant.
FWIW, I did notice that it is possible to return a literal in the OUTPUT clause, such as:
OUTPUT INSERTED.TestID, 'Check This Out'
...but this doesn't really help me.
Does anyone have a suggestion about how to accomplish what I am trying to do?
October 9, 2008 at 12:49 pm
You could query for the ID later based on the SomeData value.
Take an example where you are inserting bunch of professors and bunch of classes and need to link them together. You insert all the professors first, then all the classes, then insert into the ProfessorTeaches table by querying for ProfessorID and ClassID from Professors and Classes using the professor's name and the class name.
What about duplicates, you might ask? Well, if you have two rows that are basically the same except for the ID:
2 | Professor Jones
6 | Professor Jones
Then I'd say you shouldn't have inserted the second one - there isn't any difference between the two.
If this doesn't work for you, can you be more specific with the type of data you are working with?
October 9, 2008 at 4:11 pm
Chad,
Thank you for taking the time to reply.
In this case, I don't know or have control over what columns are defined in #Test or #Source. (I am not guaranteed that there will be a column named SomeData for example.) The schema for these tables can change based on runtime configurations specified by the user.
Similarly, I don't know or have control over what is going to be inserted: it is possible that the user explicitly wants two identical rows, such as in an Order -> OrderDetail environment where OrderDetails must be quantity 1 if the item is a serialized part.
What I am doing now works: I temporarily use a column in the table into which I am inserting (a column that I know will always be present) to store a value that I can use to join back to the original source data--much as you suggested, but using a single integer key instead of relying on field values to do the match up.
It would be a nice extension to the OUTPUT clause to pass other columns through. In the absense of that, I suppose storing a "bread crumb" in the target table and subsequently doing a JOIN is the best I can do.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply