inserting random values into a column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

inserting random values into a column

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources