Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
Use the right tool to get identity values back after an insert
-- First, lets create a couple of tables with identity columns: CREATE TABLE TblIdentityDemo ( id int identity(1,1), col int NOT NULL CONSTRAINT PK_TblIdentityDemo PRIMARY KEY ); CREATE TABLE TblTriggeredWithIdentity ( id int identity(-10,-10), col int CONSTRAINT DF_TblTriggeredWithIdentity_Col DEFAULT(0) ); GO -- Then, create an after insert trigger on one table, that will insert records to the other table. CREATE TRIGGER TblIdentityDemo_Insert ON TblIdentityDemo FOR INSERT AS INSERT INTO TblTriggeredWithIdentity DEFAULT VALUES GO -- And now - let's play: First, insert a couple of records into the first table: INSERT INTO TblIdentityDemo (Col) VALUES (1), (2); -- See what happens: SELECT TOP 1 'After a succesful insert' As 'demo', SCOPE_IDENTITY() As [Scope_Identity()], IDENT_CURRENT('TblIdentityDemo') As [IDENT_CURRENT('TblIdentityDemo')], @@IDentity As [@@IDentity], Id, Col FROM TblIdentityDemo ORDER BY Id DESC; -- Then, a failed insert (Col is the primary key, so no duplicate values allowed!) BEGIN TRY INSERT INTO TblIdentityDemo (Col) VALUES (1), (2); END TRY BEGIN CATCH -- Do nothing END CATCH -- Now see what happens: SELECT TOP 1 'After a failed insert' As 'demo', SCOPE_IDENTITY() As [Scope_Identity()], IDENT_CURRENT('TblIdentityDemo') As [IDENT_CURRENT('TblIdentityDemo')], @@IDentity As [@@IDentity], Id, Col FROM TblIdentityDemo ORDER BY Id DESC; -- And finnaly, a taste of the output clause: INSERT INTO TblTriggeredWithIdentity (Col) OUTPUT 'output' as 'demo', Inserted.id, Inserted.Col VALUES (1), (2), (3), (4);
run
|
edit
|
history
|
help
0
zani
BRYANActul
Exam_1
20181CSE0068
test1
SQL left join
Practice
Mahi
SQL Server - regex filtering using LIKE
a