158

I've made some modifications to my database and I need to migrate the old data to the new tables. For that, I need to fill a table (ReportOptions) taking the data from the original table (Practice), and fill a second intermediate table (PracticeReportOption).

ReportOption (
    ReportOptionId int PK, 
    field1, field2...
)
Practice (
    PracticeId int PK, 
    field1, field2...
)
PracticeReportOption (
    PracticeReportOptionId int PK, 
    PracticeId int FK, 
    ReportOptionId int FK, 
    field1, field2...
)

I made a query to get all the data I need to move from Practice to ReportOptions, but I'm having trouble filling the intermediate table.

--Auxiliary tables
DECLARE @ReportOption TABLE (
    PracticeId int, -- This field is not on the actual ReportOption table
    field1, field2...
)
DECLARE @PracticeReportOption TABLE (
    PracticeId int, 
    ReportOptionId int, 
    field1, field2
)

--First I get all the data I need to move
INSERT INTO @ReportOption
SELECT P.practiceId, field1, field2...
  FROM Practice P

--I insert it into the new table,
--but somehow I need to have the repation PracticeId / ReportOptionId
INSERT INTO ReportOption (field1, field2...)
OUTPUT @ReportOption.PracticeId, --> this is the field I don't know how to get
       inserted.ReportOptionId
  INTO @PracticeReportOption (PracticeId, ReportOptionId)
SELECT field1, field2
  FROM @ReportOption

-- This would insert the relationship, If I knew how to get it!
INSERT INTO @PracticeReportOption (PracticeId, ReportOptionId)
SELECT PracticeId, ReportOptionId
  FROM @ReportOption

If I could reference a field that is not from the destination table in the OUTPUT clause, that would be great (I think I can't, but I don't know for sure). Any ideas on how to accomplish my need?

CarenRose
  • 1,266
  • 1
  • 12
  • 24
Alejandro B.
  • 4,807
  • 2
  • 33
  • 61
  • 1
    You can return any of the columns of the table you're inserted a row into, in your `OUTPUT` clause. So even if you don't provide a value for a given column in your `INSERT` statement, you can still specify that column in the `OUTPUT` clause. You can however not return SQL variables or columns from other tables. – marc_s Jun 08 '12 at 13:36
  • 2
    @marc_s thanks for your reply, but I don't have the field I need in the destination table (I need PracticeId, which is not on ReportOption) – Alejandro B. Jun 08 '12 at 14:03

2 Answers2

239

You can do this by using MERGE instead of INSERT.

So replace this:

INSERT INTO ReportOption (field1, field2...)
OUTPUT @ReportOption.PracticeId, --> this is the field I don't know how to get
       inserted.ReportOptionId
  INTO @PracticeReportOption (PracticeId, ReportOptionId)
SELECT field1, field2
  FROM @ReportOption

with:

MERGE INTO ReportOption USING @ReportOption AS temp ON 1 = 0
WHEN NOT MATCHED THEN
    INSERT (field1, field2)
    VALUES (temp.Field1, temp.Field2)
    OUTPUT temp.PracticeId, inserted.ReportOptionId, inserted.Field1, inserted.Field2
    INTO @PracticeReportOption (PracticeId, ReportOptionId, Field1, Field2);

The key is to use a predicate that will never be true (1 = 0) in the merge search condition, so you will always perform the insert, but have access to fields in both the source and destination tables.


Here is the entire code I used to test it:

CREATE TABLE ReportOption (
    ReportOptionID INT IDENTITY(1, 1), 
    Field1 INT, 
    Field2 INT
)
CREATE TABLE Practice (
    PracticeID INT IDENTITY(1, 1), 
    Field1 INT, 
    Field2 INT
)
CREATE TABLE PracticeReportOption (
    PracticeReportOptionID INT IDENTITY(1, 1), 
    PracticeID INT, 
    ReportOptionID INT, 
    Field1 INT, 
    Field2 INT
)

INSERT INTO Practice VALUES (1, 1), (2, 2), (3, 3), (4, 4)


MERGE INTO ReportOption r USING Practice p ON 1 = 0
WHEN NOT MATCHED THEN
    INSERT (field1, field2)
    VALUES (p.Field1, p.Field2)
    OUTPUT p.PracticeId, inserted.ReportOptionId, inserted.Field1, inserted.Field2
    INTO PracticeReportOption (PracticeId, ReportOptionId, Field1, Field2);

SELECT *
FROM PracticeReportOption

DROP TABLE ReportOption
DROP TABLE Practice
DROP TABLE PracticeReportOption 

More reading, and the source of all that I know on the subject is here.

CarenRose
  • 1,266
  • 1
  • 12
  • 24
GarethD
  • 68,045
  • 10
  • 83
  • 123
  • 2
    Thanks, this does the trick! I was going to use a fake temp field but this is much more elegant. – Alejandro B. Jun 08 '12 at 14:12
  • 1
    Excellent! This trick is a grain of gold! Added to the first row in the collection! – Vadim Loboda Sep 24 '13 at 06:54
  • 1
    Suweet! I wish it didn't have to use the occasionally buggy MERGE command, but it's perfectly elegant otherwise. – Tab Alleman Jan 09 '17 at 19:55
  • Ha! I just re-invented this solution before I saw this thread (and having never previously used or even heard of `Merge` Statement). I was trying to `Output` my `Insert`'s to a mapping Table that included `Identity` value from target `Table` with a non-`Insert`'ed value from the `Select` - `From` Table. Without this solution, I imagine I'd have to: a) start `Transaction`, b) get next `Identity`, c) `Insert` into temp `Table` with computed `Identity`, d) set `Identity_Insert`, e) `Insert` into target `Table` from temp `Table`, f) clear `Identity_Insert`. – Tom May 03 '18 at 22:43
  • 5
    Be warned. I used a merge statement that over the past year has grown with usage. We started having timeouts during saves and it turned out that because the merge statement always locks the tables we were having 35-160 seconds of table locking every 4 minutes. I'm having to reconstruct several merge statements to use insert/updates and limiting the number of rows they update to 500 per insert/update to avoid table locking. I estimate that this very important table was being kept locked nearly 2 1/2 hours per day which was causing everything from slow saves to timeouts. – CubeRoot Feb 04 '19 at 21:58
  • Link needs a login. Is it this one? http://dataeducation.com/dr-output-or-how-i-learned-to-stop-worrying-and-love-the-merge/ – AjV Jsy Feb 07 '19 at 13:48
  • @AjVJsy Yeah, that looks like it. I have updated the link in the answer. Cheers – GarethD Feb 07 '19 at 14:10
  • My problem with MERGE statement instead off INSERT is, that I cannot freely use `NEXT VALUE FOR` statement in MERGE. That's why I prefer INSERT in some cases. Sad, that OUTPUT in an INSERT ... SELECT ... FROM statement cannot access fields from the FROM clause. – Bernhard Döbler Mar 15 '19 at 10:19
  • 5
    Also, a deal breaker for many, is that MERGE has a whole lot of unfixed bugs in it, which surface under weird conditions. E.g. see [this article](https://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/) by Aaron Bertrand. Microsoft is refusing to fix some of them, and my secret suspicion is that MS deprecated their whole [MS Connect](http://connect.microsoft.com) service just to try and make us forget about all those bugs in the MERGE statement they don't want to fix. – Reversed Engineer Jul 29 '19 at 14:43
  • Example 'E' in https://learn.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-ver15#examples suggests you can reference other columns ... although when I try it doesn't work and I need this MERGE trick. Is this something that should work nowadays in SQL 2019? – Rory Oct 08 '21 at 11:32
  • 1
    @Rory Unfortunately not., even in SQL Server 2012 unused columns could be used in the `OUTPUT` for deletes and updates, just not inserts, and it appears in 2019 there is no advance on that position. It is a bit odd, however I suspect there's a good reason. I am sure if it piqued your interest enough, you could ask on dba.stackexchange.com and someone who truly understands what goes on under the hood would be able to answer the question as to why this design decision was made. Unfortunately I have no clue – GarethD Oct 08 '21 at 15:24
  • @GarethD - thanks! I hadn't noticed that this problem specifically related to INSERT statements, although re-reading the docs I see they do say _from_table_name_ can be used in DELETE, UPDATE, or MERGE statements ... i.e. not INSERT. Of course it's an INSERT statement I'm interested in. – Rory Oct 08 '21 at 18:05
17

Maybe someone who uses MS SQL Server 2005 or lower will find this answer useful.


MERGE will only work for SQL Server 2008 or higher.

For the rest, I found another workaround which will give you the ability to create kind of mapping tables.

Here's how Resolution will look like for SQL 2005:

DECLARE @ReportOption TABLE (
    ReportOptionID INT IDENTITY(1, 1), 
    Field1 INT, 
    Field2 INT
)
DECLARE @Practice TABLE(
    PracticeID INT IDENTITY(1, 1), 
    Field1 INT, 
    Field2 INT
)
DECLARE @PracticeReportOption TABLE(
    PracticeReportOptionID INT IDENTITY(1, 1),
    PracticeID INT, 
    ReportOptionID INT, 
    Field1 INT, 
    Field2 INT
)

INSERT INTO @Practice (Field1, Field2) VALUES (1, 1)
INSERT INTO @Practice (Field1, Field2) VALUES (2, 2)
INSERT INTO @Practice (Field1, Field2) VALUES (3, 3)
INSERT INTO @Practice (Field1, Field2) VALUES (4, 4)

INSERT INTO @ReportOption (field1, field2)
    OUTPUT INSERTED.ReportOptionID, INSERTED.Field1, INSERTED.Field2 
        INTO @PracticeReportOption (ReportOptionID, Field1, Field2)
    SELECT Field1, Field2 
    FROM @Practice 
    ORDER BY PracticeID ASC;


WITH CTE AS ( 
    SELECT PracticeID, 
        ROW_NUMBER() OVER ( ORDER BY PracticeID ASC ) AS ROW 
    FROM @Practice
)
UPDATE M 
SET M.PracticeID = S.PracticeID 
FROM @PracticeReportOption AS M
JOIN CTE AS S ON S.ROW = M.PracticeReportOptionID

SELECT * FROM @PracticeReportOption

The main trick is that we are filling the mapping table twice with ordered data from the source and destination table.

For more details, see Merging Inserted Data Using OUTPUT in SQL Server 2005.

CarenRose
  • 1,266
  • 1
  • 12
  • 24
Val
  • 529
  • 4
  • 13
  • 3
    This wouldn't solve my prob. I need to `Output` my `Insert`'s to an output `Table` that includes an `Identity` value from the target `Table` with a non-`Insert`-ed value (the PK) from the source `Table` (btw, so I could then (in a different Batch) use that output `Table` to populate a `Column` in the source `Table` with the `Identity` value). Without a `Merge`, I imagine I'd have to: a) start `Transaction`, b) get next `Identity`, c) Insert into temp `Table` with computed `Identity`, d) set `Identity_Insert`, e) `Insert` into target `Table` from temp `Table`, f) clear `Identity_Insert`. – Tom May 04 '18 at 15:43