0

I have 2 tables, the first table contains the minor details on the item and the second table contains the rest of the information.

BEGIN TRANSACTION;

INSERT INTO Items (Name, CategoryId) 
VALUES ('My Tv', 5);

INSERT INTO CharsToItem (ItemId, Value) 
VALUES ((SELECT Id FROM Items WHERE Id = @@Identity;), '65 Inch');

INSERT INTO CharsToItem (ItemId, Value) 
VALUES ((SELECT Id FROM Items WHERE Id = @@Identity;), '3840x2160');

COMMIT;

At the first insert to the CharToItem table, everything works perfectly, at the second time it says the return value is NULL and I can't insert it to the database.

Is there a way to save the Id in the select and use it several times?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Adir
  • 29
  • 2
  • 9

2 Answers2

2

After the first insert into the CharsToItem table, @@Identity returns the id of newly inserted item in CharsToItem.

Therefore, after inserting into Items, store the id in a variable

DECLARE @itemId int -- Variable to store the last ItemId.

BEGIN TRANSACTION;

INSERT INTO Items (Name, CategoryId) VALUES ('My Tv', 5);
SET @itemId = SCOPE_IDENTITY();  -- Store the new ItemId

INSERT INTO CharsToItem (ItemId, Value) VALUES (@itemId, '65 Inch');
INSERT INTO CharsToItem (ItemId, Value) VALUES (@itemId, '3840x2160');

COMMIT;

Note that you don't have to SELECT the last id from something, @@IDENTITY or SCOPE_IDENTITY() return this last id already.

Note also that SCOPE_IDENTITY() is safer than @@IDENTITY, as @@IDENTITY could return an id created by a trigger, for instance.

See this answer to this SO question: scope_identity vs ident_current.

Olivier Jacot-Descombes
  • 104,806
  • 13
  • 138
  • 188
1
DECLARE @ItemId int;

BEGIN TRANSACTION;

INSERT INTO Items (Name, CategoryId) VALUES ('My Tv', 5);

SET @ItemId = scope_identity();

INSERT INTO CharsToItem (ItemId, Value) VALUES (@ItemId, '65 Inch');
INSERT INTO CharsToItem (ItemId, Value) VALUES (@ItemId, '3840x2160');

COMMIT;
Vincent
  • 842
  • 7
  • 13