How to insert a value into a table and immediately use identy in the adjacent column?

Question

SQL Server

Have a nice day, everyone!
How can inserting a row into the table at the same time get the id of the record to use it in the adjacent column?
For example,

CREATE TABLE [dbo].[Product]([Id] [int] IDENTITY(1,1) NOT NULL,[Name] [nvarchar](50) NOT NULL)
insert into dbo.Product (Name) values ('Новый продукт_')

while select of this record should return "New product_123" - where 123 is the record id.
It is important that not through chaining or something else, but in the Name field, the id should be attached at the end of the name.
I managed to put together a solution like this:

insert into dbo.Product (Name) values ('Новый продукт_' + cast(IDENT_CURRENT('dbo.Product') as varchar(5)))

It turns out exactly what you need, but there is a problem - if you insert records at the same time, then IDENT_CURRENT will return exactly the last id, and not the current one for this row.
How to get the current one?
PS I understand what can be taken from inserted and then updated, or in other ways that entail a subsequent update, but I'm looking for a solution within a single insert.

Answer 1

CREATE TABLE [dbo].[Product](
  [Id] [int] IDENTITY(1,1) NOT NULL,
  [Name] [nvarchar](50) NOT NULL,
  [NamePower]  AS (([Name]+'_')+CONVERT([varchar],[ID])),
 CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED 
(
  [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

in the Name field, id should be attached at the end of the name

Violation of the normal form.

can there be solutions in this case?

With a calculated field, you can cast everything to the same form using the CASE statement.