| Author |
Topic |
|
misterraj
Yak Posting Veteran
94 Posts |
 Posted - 2014-06-29 : 09:06:38
|
I have a table TABLE1 AND HAVE 100 RECORDS. I have a column COL1 in the table. I need to insert random values(1 to 100) in these columns for all the records.
Can any one please help me. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2014-06-29 : 09:48:38
|
100 unique and "random" values.
DECLARE @ID INT = 0;
UPDATE dbo.Table1
SET @ID = Col = @ID + 1;
Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
 |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2014-06-30 : 13:50:51
|
you can also use NEWID to generate a random number:SELECT (ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 100) + 1 |
|
|
|
misterraj
Yak Posting Veteran
94 Posts |
Posted - 2014-07-01 : 07:17:26
|
DECLARE @ID INT = 0;
UPDATE dbo.Table1
SET @ID = Col = @ID + 1;
this inserts values 1, 2, 3 .... sequentially in the order of the rows. I need random values..
for eg:
In row1, value should be like 9.
In row2, value should be like 19.
In row3 value should be like 27 (or any other value).
like this...
|
|
|
|
misterraj
Yak Posting Veteran
94 Posts |
Posted - 2014-07-01 : 07:19:18
|
Ok I got. this worked.
UPDATE dbo.MockExamQuestion
SET Orderby = (ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) % 100) + 1
thanks Lamprey. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2014-07-01 : 07:36:02
|
And you are OK with duplicate values?
Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2014-07-01 : 07:37:42
|
[code]UPDATE f
SET OrderBy = rn
FROM (
SELECT OrderBy,
ROW_NUMBER() OVER (ORDER BY NEWID()) AS rn
FROM dbo.MockExamQuestion
);[/code]
Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
|
misterraj
Yak Posting Veteran
94 Posts |
Posted - 2014-07-06 : 12:08:23
|
| no issues with duplicate values |
|
|
|
misterraj
Yak Posting Veteran
94 Posts |
Posted - 2014-07-06 : 12:13:42
|
WHAT IS f ?
when i execute this,
UPDATE f
SET OrderBy2 = rn
FROM (
SELECT OrderBy2,
ROW_NUMBER() OVER (ORDER BY NEWID()) AS rn
FROM MockExamQuestion where category ='MockExam1'
)
i get incorrect syntax near ) |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2014-07-06 : 12:28:16
|
[code]UPDATE f
SET OrderBy = rn
FROM (
SELECT OrderBy,
ROW_NUMBER() OVER (ORDER BY NEWID()) AS rn
FROM dbo.MockExamQuestion
) AS f;[/code]
Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
|
|
inserting random values into a column