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.
Using OUTPUT clause to insert value not in INSERTED
Use MERGE
instead of INSERT
:
MERGE
INTO trn_temp d
USING (
SELECT D.DET_DATE, 'SOMETEXT' AS sometext, SUM(D.DET_NET) AS the_sum
...
) s
ON (1 = 0)
WHEN NOT MATCHED THEN
INSERT (TRN_TRAN_DATE, TRN_DESCRIPTION, TRN_AMT)
VALUES (det_date, sometext, the_sum)
OUTPUT s.*
Update:
To work around the GROUP BY
problem, use this:
DECLARE @tmp TABLE
(
det_primary INT NOT NULL PRIMARY KEY
)
MERGE
INTO register r
USING detail d
ON (r.det_primary_link = d.det_primary)
WHEN NOT MATCHED THEN
INSERT (det_primary_link, ins_date)
VALUES (det_primary, GETDATE())
OUTPUT d.det_primary
INTO @tmp;
INSERT
INTO trn_temp (trn_tran_date, trn_description, trn_amt)
OUTPUT INSERTED.*
SELECT det_date, 'sometext', SUM(det_net)
FROM @tmp t
JOIN detail d
ON d.det_primary = t.det_primary
GROUP BY
det_date
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
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)
How do I use an INSERT statement's OUTPUT clause to get the identity value?
You can either have the newly inserted ID being output to the SSMS console like this:
INSERT INTO MyTable(Name, Address, PhoneNo)
OUTPUT INSERTED.ID
VALUES ('Yatrix', '1234 Address Stuff', '1112223333')
You can use this also from e.g. C#, when you need to get the ID back to your calling app - just execute the SQL query with .ExecuteScalar()
(instead of .ExecuteNonQuery()
) to read the resulting ID
back.
Or if you need to capture the newly inserted ID
inside T-SQL (e.g. for later further processing), you need to create a table variable:
DECLARE @OutputTbl TABLE (ID INT)
INSERT INTO MyTable(Name, Address, PhoneNo)
OUTPUT INSERTED.ID INTO @OutputTbl(ID)
VALUES ('Yatrix', '1234 Address Stuff', '1112223333')
This way, you can put multiple values into @OutputTbl
and do further processing on those. You could also use a "regular" temporary table (#temp
) or even a "real" persistent table as your "output target" here.
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.
Insert ... select output clause, get both INSERTED.ID and SELECTED.ID
Found an answer here: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/8cf1e38e-a29b-4ad7-abc7-b9fe2b987698/insert-into-using-select-with-output-into-multipart-identifier-could-not-be-bound?forum=transactsql
Tom:
You can't use columns from the SELECT part of an INSERT ... SELECT in
the OUTPUT clause. The only columns an INSERT statement can use in
the OUTPUT clause is columns in the inserted pseudo table.
Fortunately, there is a way around this restriction. Use MERGE to
simulate an INSERT by using an ON condition that is always false.
Here is the code:
select *
into #temp
from [dbo].[MindmapNodes]
where [MindmapId] = 215
DECLARE @mindmapNodes table(Id int, OldId int);
MERGE INTO [dbo].[MindmapNodes]
USING #temp AS cf
ON 1= 0
WHEN NOT MATCHED THEN
INSERT
( [MindmapId],
[Loc],
[Title],
[SnippetId]
)
Values
(
216,
cf.Loc,
cf.Title,
cf.SnippetId
)
Output inserted.[Id], cf.[Id] INTO @mindmapNodes;
Output Clause Adds Extra Characters Not In Originating source
The problem is that your PhysicalTable_1 contains non-printable unicode characters in LName.
You insert the unicode LName NVARCHAR column of Table1, into an ascii/nonunicode LName VARCHAR column of Table2. Nonunicode is half the size of unicode in sql server, some bytes have to be "cut-off" and because of the size reduction the non-printable characters become apparent.
--characters to binary
SELECT CAST(N'P' AS VARBINARY(10)) AS UnicodeP, CAST('P' AS VARBINARY(10)) AS AsciiP --unicode is double the size of ascii
CREATE TABLE #temp(UnicodeP NVARCHAR(10), AsciiP VARCHAR(10));
INSERT INTO #temp(UnicodeP, AsciiP) VALUES (N'P', 'P'); --nothing special, normal insertion
INSERT INTO #temp(UnicodeP, AsciiP) VALUES ('P', N'P'); --omitting the N for unicode and using N for ascii, still works ok, implicit conversion
SELECT * FROM #temp;
--use binary from the very first SELECT CAST(....
INSERT INTO #temp(UnicodeP, AsciiP) VALUES (0x5000, 0x50); --still fine
SELECT * FROM #temp;
--prepend a nonprintable character (BOM) to unicode P, just insert into the UnicodeP only
INSERT INTO #temp(UnicodeP, AsciiP) VALUES (0xFEFF + 0x5000, NULL); --still fine
SELECT * FROM #temp;
--if you copy and paste the last UnicodeP, where AsciiP is NULL, you will not notice any visual difference
--update the ascii from unicode , where ascii is null
UPDATE #temp
SET AsciiP = UnicodeP --implicit conversion, ascii is half the unicode, some bytes have to go away
WHERE AsciiP IS NULL;
--since unicode was implicitly converted to ascii, some bytes are "stripped out" The nonprintable 0xFEFF needs to be "cut in half" and it becomes an unidentified char
SELECT UnicodeP, CAST(UnicodeP AS VARBINARY(10)) AS UnicodePbinary, AsciiP, CAST(AsciiP AS VARBINARY(10)) as AsciiPbinary
FROM #temp;
DROP TABLE #temp;
*edit, implicit unicode to nonunicode and asciiOrnothing
SELECT NCHAR(rownum) AS TheChar, CAST(NCHAR(rownum) AS CHAR(1)) AS ImplicitConversion,
CASE WHEN NCHAR(rownum) < N'Ā' collate Latin1_General_BIN2 THEN NCHAR(rownum) ELSE '' END AS AsciiOrNothing,
UNICODE(NCHAR(rownum)) AS CharInteger,
--or
CASE WHEN UNICODE(/*TheChar*/ NCHAR(rownum)) <= 255 THEN NCHAR(rownum) ELSE '' END AS AsciiOrNothing2
FROM
(
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT null)) AS rownum
FROM (
--10K
SELECT TOP (100) name from master.dbo.spt_values) AS a
CROSS JOIN (SELECT TOP (100) name from master.dbo.spt_values) AS b
) AS src
ORDER BY rownum
Related Topics
Find the Byte Size of a Row in Postgresql
SQL Server 2005 - Order of Inner Joins
How to Make SQL Case Sensitive
Convert Datetime to Unix Epoch in Informix
Inserting and Transforming Data from SQL Table
Tips and Tricks to Speed Up an SQL
How to Remove Duplicate Rows Except One
SQL Trigger Cannot Do Instead of Delete But Is Required for Ntext, Image Columns
Converting Number to Words in SQL
How to Force Nolock Hint for SQL Server Logins
When Should You Consider Indexing Your SQL Tables
SQL Performance of a Lookup Table
Modify(Replace) Xml for Conditions
SQL Server Automatic Update Datetimestamp Field
Is There a Function That Takes a Year, Month and Day to Create a Date in Postgresql