I want to insert all unique random number into my column. With the below query all i am getting is same number.
INSERT INTO CurrentSessionVariableDetails
(SessionId, FirstName, LastName, AddedForId, AddedById, CompanyId, ConnectionString, DataForYear, RoleName, ManagerName, CompanyLogoPath, IncludeProfileRatings, IncludeCompetency, ManagerId, LabelForCompetency, IsPerformanceRatingWithValue, IncludeDualObjRatingRev, IncludeSignOff, IsCompetencyRatingWithValue, IncludeObjectiveRating, RecordCPD, IncludeCPDSignOff)
Select
(SELECT CAST(RAND() * 1000000 AS INT)) AS [RandomNumber],
e.FirstName
, e.LastName
, e.Id
, null as AddedById
, e.Company_Id
, 'tesstring' as ConnectionString
, m.DataForyear
, (Select Name
from Role as r with (nolock)
where r.Id=e.Role_Id
) as RoleName
, (Select emp.FirstName + ' ' + emp.LastName
from Employee as emp with (nolock)
where emp.Id=e.ManagerId) as ManagerName
, c.Logo_Path
, c.IncludeProfileRatings
, c.IncludeCompetency
, e.ManagerId
, c.labelforcompetency
, c.IsPerformanceRatingWithValue
, c.IncludeDualObjectiveRatingReview
, c.includeSignOff
, c.IsCompetencyRatingWithValue
, c.IncludeObjectiveRating
, e.RecordCPD
, c.IncludeCPDSignedOff
from Employee as e with (nolock)
inner join MidYearAppraisal as m with (nolock) on e.Id = m.MidYear_AddedFor_Id
inner join Company as c with (nolock) on c.Id=e.Company_Id
Where m.isPublished =1