SQL insert select @@Identity

Question

How to use the @@IDENTITY when I use INSERT SELECT ?

DECLARE @ENTITYID AS BIGINT

INSERT INTO Quiz_QuizQuestion
SELECT @ENTITYID,
       @DIFICULTLEVELCODE,
       ENTITYID,
       @QuizEntityId,
       Title,
       [Description],
       [Description],
       Duration
FROM   Education_Question
WHERE  EntityID = 1 --THIS SELECT RETURN JUST 1 RECORD
SET @ENTITYID = @@IDENTITY

SELECT @ENTITYID // NULL  

Answer 1

You need not insert @@IDENTITY into the table in your scenario - you have to CREATE table with IDENTITY field like this:

CREATE TABLE Quiz_QuizQuestion
(
  EntityId int IDENTITY NOT NULL,
  ...
)
GO

DECLARE @ENTITYID AS BIGINT

INSERT INTO Quiz_QuizQuestion
SELECT 
       @DIFICULTLEVELCODE,
       ENTITYID,
       @QuizEntityId,
       Title,
       [Description],
       [Description],
       Duration
FROM   Education_Question
WHERE  EntityID = 1 --THIS SELECT RETURN JUST 1 RECORD
SET @ENTITYID = SCOPE_IDENTITY()

SELECT @ENTITYID // NULL 

Answer 2

This is copied verbatim from the MSDN page about @@IDENTITY: "After an INSERT, SELECT INTO, or bulk copy statement completes, @@IDENTITY contains the last identity value generated by the statement. If the statement did not affect any tables with identity columns, @@IDENTITY returns NULL. If multiple rows are inserted, generating multiple identity values, @@IDENTITY returns the last identity value generated." (link). I don't know if you need more information than that.