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?
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.