如何将随机数插入sql server表？ [英] How to insert random numbers into sql server table ?

问题描述

我想在我的专栏中插入所有唯一的随机数。以下查询我得到的是相同的数字。

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

推荐答案

如果您关注插入唯一号码，那么

可能有两种选择。

1.添加具有自动增量属性的新列，也可以用作此表的主键。

2.如果您不想添加另一列，请使用函数ROW_NUMBER（）和ORDER子句。网上有很多文章，如 OVER CLAUSE：MSDN [ ^ ]



如果你想只插入随机数，根据MSDN RAND Function SQL：MSDN [ ^ ]RAND的重复调用具有相同种子值的（）返回相同的结果。所以有一个名为

If inserting unique number is your concern then
There could be two alternatives.
1. Add a new columns with Auto increment property and can also be used as a primary key for this table.
2. If you don't want to add another column use function ROW_NUMBER() along with ORDER clause. There are many articles on the net like OVER CLAUSE: MSDN[^]

How ever if you want to insert random numbers only, as per MSDN RAND Function SQL: MSDN[^] "Repetitive calls of RAND() with the same seed value return the same results." So there is a new function called

NEWID()

的新函数可以使用，否则你可以把你的结果带到一个变量并循环它以使用RAND（）函数返回随机数。希望这可以帮助。



问候，

Sujay C。

which can be used or else you can take your result to a variable and loop over it to return Random number using RAND() function. Hope this helps.

Regards,
Sujay C.

这篇关于如何将随机数插入sql server表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！