Insert-Output Including Column from Other Table

INSERT-OUTPUT including column from other table

I'm not sure if that is the best option, but it seems you can do the trick using MERGE :

MERGE [Contacts]  trgt
USING 
(  
  SELECT [First_Name], [Last_Name], g.[GUID] as [GUID]
  FROM [SourceTable] s
  JOIN @guids g ON s.[GUID] = g.[GUID]
)src ON (1=0)
WHEN NOT MATCHED THEN INSERT ( [FirstName], [LastName], [ModifiedDate] )
 VALUES (src.[First_Name],src.[Last_Name], GETDATE())
OUTPUT [inserted].[ContactID], src.[GUID]
INTO @contacts

SQL insert and update data into column based on other Table column

Use an update join:

UPDATE PRODUCTS p
INNER JOIN SUPPLIERS s
    ON s.SupplierName = p.ProductSupplier
SET p.SupplierID = s.SupplierID;

Note that you are moving in the direction of more normalization, which is a good thing. Assuming you are intending to keep the SUPPLIER table, then the ProductSupplier column in the PRODUCTS table is now redundant and can probably be dropped:

ALTER TABLE PRODUCTS DROP COLUMN ProductSupplier;

Add source columns to output clause with insert into select

When you do an insert into MSSQL, all data inserted is temporarily stored. You can acces the data by using an OUTPUT clause. In this output clause you have two ways of accessing data. Inserted.* (or specified column) and Deleted.. This way you can capture the same data that was either inserted or deleted, and route it to another table. With an Insert statement, you can only use OUTPUT inserted., with Delete only OUTPUT deleted.* and with update both (an update is after all nothing but a delete, and an insert of the same row with 1 or more cells changed)

The syntax is as follows:

Insert Into table1(table1_ID, col1)
Output inserted.tabl1_id, inserted.col1 into table2
from .... (or Values()? )

After the insert you can do an OUTPUT inserted.col1, inserted.col2 etc INTO table2. For update and delete you can use the same syntax (OUTPUT deleted.* into <2nd table>)

If you would have to copy the data to multiple tables, you can also store it in a table variable or a temp table. Note, you cant store it anything but a table, even if you know it will return just a single row. This is because while you might know it will always return 1 row, SQL doesnt and wont allow this to be a problem.

Using OUTPUT INTO with from_table_name in an INSERT statement

No, because an INSERT doesn't have a FROM; it has a set of values that are prepared either by the VALUES keyword, or from a query (and even though that query has a FROM, you should conceive that it's already been run and turned into a block of values by the time the insert is done; there is no s.code any more)

If you want to output something from the table that drove the insert you'll need to use a merge statement that never matches any records (so it's only inserting) instead, or perhaps insert all your data into @tmp and then insert from @tmp into the real table - @tmp will thus still be the record of rows that were inserted, it's just that it was created to drive the insert rather than as a consequence of it (caveats that it wouldn't contain calculated columns)

insert into a table from another table with different columns

This should work I think

INSERT INTO A (load, Roll, dest)
SELECT load, Roll, dest
FROM B

Is it possible to for SQL Output clause to return a column not being inserted?

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.

Insert into with output clause

If table 1 and table 2 have a 1:1 relationship, and no foreign key exists between the two then you could do this in a single statement:

MERGE Table1 AS a 
USING
(   SELECT  A.[group], A.account, B.title, B.amount, B.id2    
    FROM    Table1 AS A
            LEFT OUTER JOIN Table2 AS B
                ON A.id = B.id2
) AS b
    ON 1 = 0
WHEN NOT MATCHED THEN 
    INSERT ([group], account)
    VALUES (b.[group], b.account)
OUTPUT inserted.Id, B.title, B.amount 
    INTO Table2(id2, title, amount);

Example on SQL Fiddle

Realistically though, if your tables are related they should have a foreign key, and in most cases they won't be 1:1, rather 1:n.

In which case you would still need to use MERGE to caputre both the new ID and the old ID, but you would then need to capture this mapping in a temporary table before performing a second insert to Table2:

DECLARE @Map TABLE (OldID INT NOT NULL, NewID INT NOT NULL);

MERGE Table1 AS a 
USING
(   SELECT  A.ID, A.[group], A.account  
    FROM    Table1 AS A
) AS b
    ON 1 = 0
WHEN NOT MATCHED THEN 
    INSERT ([group], account)
    VALUES (b.[group], b.account)
OUTPUT inserted.Id, b.ID
    INTO @Map(NewID, OldID);

INSERT Table2 (id2, title, amount)
SELECT  m.NewID, b.title, b.amount
FROM    @Map AS m
        INNER JOIN Table2 AS b
            ON b.ID2 = m.OldID;

Example on SQL Fiddle

Return a source table column in an INSERT .. OUTPUT .. SELECT query

INSERT OUTPUT can only output columns from the table you're inserting into. MERGE can output source tables as well, eg

declare @keyMapping table(oldId int, [newId] int)

merge into T2 
using (select ID, X, Y, Z from T1) as src 
on 1=2
when not matched then insert (X,Y,Z) VALUES (src.X,src.Y,src.Z)
output inserted.ID, src.ID into @keyMapping(newId,oldId);

Output columns not in destination table?

Look at Example A:

DECLARE @temp TABLE (DocumentID int)

INSERT INTO FinishedDocument 
    OUTPUT INSERTED.DocumentID 
    INTO @temp
SELECT DocumentID 
FROM Document 
WHERE DocumentID = @DocumentID 

SELECT Document.DocumentId, Document.Description
FROM @temp AS t
INNER JOIN Document 
    ON t.DocumentID = Document.DocumentID

---

Related Topics