filling database table randomly

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

General SQL Server Forums

New to SQL Server Programming

filling database table randomly

Author

Topic

belosss
Starting Member

9 Posts

Posted - 2006-05-18 : 06:48:55

Hi there I need to fill database table randomly with 5 ordered numbers such as 43566 , 78578 , 92565 , .. to gain approximately 100000 row of a table . Is there a query ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-18 : 06:58:58

you can use rand() or newid() to generate random value for your table, and inner join with number table to generate the required number of rows

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-18 : 07:00:07

[code]

Declare @tblTmp Table
(
tmp int
)
Declare @i int
Set @i =1
While @i<=10000
BEgin
Insert @TblTmp
Select Cast (Rand() * 10000 As int)
Set @i=@i+1
End
Select * From @TblTmp
[/code]

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-18 : 07:01:10

Do you want to fill the table with five digits random numbers?
See if this helps
http://weblogs.sqlteam.com/jeffs/archive/2004/11/22/2927.aspx

Madhivanan

Failing to plan is Planning to fail

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-05-18 : 07:15:06

This will give you 1 million rows of numbers that are in the range of 0 to 99,999.

The F_TABLE_NUMBER_RANGE is available here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685


select
	num1 = abs(convert(int,convert(varbinary(20),newid())))%100000 ,
	num2 = abs(convert(int,convert(varbinary(20),newid())))%100000 ,
	num3 = abs(convert(int,convert(varbinary(20),newid())))%100000 ,
	num4 = abs(convert(int,convert(varbinary(20),newid())))%100000 ,
	num5 = abs(convert(int,convert(varbinary(20),newid())))%100000 
from
	-- Function available in Script Liprary Forum
	dbo.F_TABLE_NUMBER_RANGE(1,1000000)

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-20 : 01:27:57

>>I also want to know that how could I insert that randomly created tables to my own database table ?

Why do you want to do this?

Madhivanan

Failing to plan is Planning to fail

turkalpk
Starting Member

21 Posts

Posted - 2006-05-22 : 15:58:29

quote:
Originally posted by madhivanan

>>How could I insert that randomly created tables to my own database table ? It creates database with name tmp but I could not locate it to my database .

Why do you want to do this?

Madhivanan

Failing to plan is Planning to fail

I have a project about data mining and I have to do some tests thus I need to extend my database . Please help .

F16 LÝGHTÝNÝNNNG

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-23 : 01:51:55

If it is test then do(but not recommended to implement this into your production server)

Declare @table varchar(10)
Select @table ='Test_'+replace(left(newid(),10),'-','')
Select @table
EXEC ('Create table '+@table+'(i int)')

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources