Output Inserted.Id and Another Field

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



Leave a reply



Submit