Output Inserted.Id AND another field
You might want to explore MERGE
:
MERGE INTO dbo.Products
USING dbo.BulkProducts AS src
ON 1 = 0 -- Never match
WHEN NOT MATCHED THEN
INSERT(EanCode, ChangedDateTime, ChangedById, Deleted)
VALUES(src.EanCode, GETDATE(), GETDATE(), 0)
OUTPUT
inserted.Id,
src.Id
INTO @OutProduct;
Reference:
Dr. OUTPUT or: How I Learned to Stop Worrying and Love the MERGE by Adam Machanic
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.
Combine OUTPUT inserted.id with value from selected row
You can (ab)use MERGE
with OUTPUT
clause.
MERGE
can INSERT
, UPDATE
and DELETE
rows. In our case we need only to INSERT
.
1=0 is always false, so the NOT MATCHED BY TARGET
part is always executed.
In general, there could be other branches, see docs. WHEN MATCHED
is usually used to UPDATE
; WHEN NOT MATCHED BY SOURCE
is usually used to DELETE
, but we don't need them here.
This convoluted form of MERGE
is equivalent to simple INSERT
,
but unlike simple INSERT
its OUTPUT
clause allows to refer to the columns that we need.
It allows to retrieve columns from both source and destination tables thus saving a mapping between old and new IDs.
MERGE INTO [dbo].[Test]
USING
(
SELECT [Data]
FROM @Old AS O
) AS Src
ON 1 = 0
WHEN NOT MATCHED BY TARGET THEN
INSERT ([Data])
VALUES (Src.[Data])
OUTPUT Src.ID AS OldID, inserted.ID AS NewID
INTO @New(ID, [OtherID])
;
Regarding your update and relying on the order of generated IDENTITY
values.
In the simple case, when [dbo].[Test]
has IDENTITY
column, then INSERT
with ORDER BY
will guarantee that the generated IDENTITY
values would be in the specified order. See point 4 in Ordering guarantees in SQL Server. Mind you, it doesn't guarantee the physical order of inserted rows, but it guarantees the order in which IDENTITY
values are generated.
INSERT INTO [dbo].[Test] ([Data])
SELECT [Data]
FROM @Old
ORDER BY [RowID]
But, when you use the OUTPUT
clause:
INSERT INTO [dbo].[Test] ([Data])
OUTPUT inserted.[ID] INTO @New
SELECT [Data]
FROM @Old
ORDER BY [RowID]
the rows in the OUTPUT
stream are not ordered. At least, strictly speaking, ORDER BY
in the query applies to the primary INSERT
operation, but there is nothing there that says what is the order of the OUTPUT
. So, I would not try to rely on that. Either use MERGE
or add an extra column to store the mapping between IDs explicitly.
Store the value of output inserted._ID to local variable to reuse it in another query
IDENTITY COLUMN
If it is an identity column and you are only inserting a single row then you can use SCOPE_IDENTITY()
function to get the last generated Identity value within the scope of the current session.
DECLARE @NewValue INT;
insert into V_Post
values('Please return the id of THIS row :)')
SET @NewValue = SCOPE_IDENTITY()
IF NOT IDENTITY Column Or Multiple Identity values
If it is an identity column and you are inserting multiple rows and want to return all the newly inserted Identity values, or it is not an identity column but you need the last inserted value then you can make sure of OUTPUT
command to get the newly inserted values.
DECLARE @tbl TABLE (Col1 DataType)
DECLARE @NewValue Datatype;
insert into V_Post
output inserted._ID INTO @tbl
values('Please return the id of THIS row :)')
SELECT @NewValue = Col1 FROM @tbl
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 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
Output Inserted T-SQL
The typical code is:
declare @ids table (id int);
insert into [dbo].[TEP_Payments_Table] ([col1], [col2])
output inserted.id into @ids
values ('testval1', 'testval2');
Your version would probably work if the output
clause were before the values
clause.
How to insert a row into another table using last inserted ID?
You don't need a trigger or any of the @@IDENTITY
, SCOPE_IDENTITY()
functions. All of them have restrictions and none of them can deal with values that aren't produced by an IDENTITY
constraint. None of them can deal with multiple insertions either.
You can use the OUTPUT clause of INSERT
to copy newly inserted values into another table or a table variable.
OUTPUT is also available for UPDATE and DELETE. You can retrieve the new/modified columns with the inserted.
and deleted.
prefixes
For these tables :
create table #GamerName
(
ID int IDENTITY primary key,
Name nvarchar(20) not null
);
create table #GamerValues(
ID int IDENTITY primary key,
GamerID int not null,
Score int not null
);
You can insert new records in #GamerName
and copy the generated ID to #GamerValues
with :
INSERT INTO #GamerName (Name)
OUTPUT inserted.ID,0 into #GamerValues(GamerID,Score)
VALUES
('Jeff'),
('Geoff'),
('Jarrod'),
New values appear in the inserted
virtual table. OUTPUT is also availa
A new line will be created for each of the gamers in GamerValues. Let's modify the default score with :
UPDATE #GamerValues
SET Score=100
The table will look like :
ID GamerID Score
1 1 100
2 2 100
3 3 100
Adding another gamer with
insert into #GamerName (Name)
output inserted.ID,0 into #GamerValues(GamerID,Score)
Values
('Joel sp')
Will result in a new line with a Score of 0
ID GamerID Score
1 1 100
2 2 100
3 3 100
4 4 0
Related Topics
Transpose Rows into Columns in SQL Server 2008 R2
Microsoft Access Query Should Return True or True and False, Only Returns True
Select * from Table or Select Id,Field1, Field2, Field3 from Table - Best Practice
Unique Constraint Controlled by a Bit Column
SQL Server: How to Group Multiple Row Values into Separate Columns
How to Use SQL Server Compact Edition Ce on Mono
F# Type Provider for SQL in a Class
How to Perform the Same Aggregation on Every Column, Without Listing the Columns
How to Create a Dates Table in Redshift
Concat Group by in Vertica SQL
Remove Duplicate Rows in a Table
Grant Access to Just One Schema in Postgresql
Duplicate Groups of Records to Fill Multiple Date Gaps in Google Bigquery