inserting random records
-
I m an SQL newbie and i have to insert 100 new records, but the phone number must have first 5 numbers randomly choosed(i guess rand statement ,i just dont know HOW to use it)..Now, i know how to insert new records, but this random thing is giving me a hard time...HEEEELP!!!
-
Hi,
Don't know how to do this with rand() but...
Have a look at this.
#my_phone_numbers is "your" table with phone numbers.
HTH
Leon
create table #my_phone_numbers (phone_number varchar(5) not null)
set nocount on
declare @digits int
set @digits = 0
declare @digit int
declare @phone_number varchar(5)
declare @number_of_phone_digits int
declare @record_count int
set @record_count = 1
set nocount on
--fill a temporary table with numbers 0 to 9
create table #digits (digit int not null)
while @digits <= 9
begin
insert into #digits
values(@digits)
set @digits = @digits + 1
end
while @record_count <= 100
begin
set @phone_number = ''
set @number_of_phone_digits = 1
while @number_of_phone_digits <= 5
begin
--get new number for phone number
set @digit = (select top 1 digit from #digits order by newid())
--build new phone number
set @phone_number = @phone_number + cast(@digit as char(1))
--keep count of number of digits in current string
set @number_of_phone_digits = @number_of_phone_digits + 1
end
--insert new number into the table
insert into #my_phone_numbers values (@phone_number)
--keep count of number of added records
set @record_count = @record_count + 1
end
select * from #my_phone_numbers
drop table #digits
drop table #my_phone_numbers
set nocount off
Regards,
Leon Bakkers -
RAND is a very poor randomizer. NEWID does a much better job using GUIDS but with regards to first 5 digits being the random value this may make a bit difficult but this is what you could try.
Create a table containing all possible 5 digits combinations if all then I can help just need to know if there is any rules such as first number cannot be 0 or anything else.
Next using NEWID() against that table do a
SELECT TOP 1 numbcol FROM tblNums ORDER BY NEWID()
Should be quite random
If using SQL 2000 this can be in a function to use inline if insert is based on values.
If based on SELECT data into INSERT then you could do a join to match rows up.
Just need to know how it goes in and what you are expecting to result.
-
Hi,
This is with using newid() as randomizer:
declare @digits table(nr int)
insert @digits(nr) select 0 union select 2 union select 4 union select 6 union select 8
insert @digits(nr) select nr+1 from @digits
select top 100 a.nr+10*b.nr+100*c.nr+1000*d.nr+10000*e.nr
from @digits a cross join @digits b cross join @digits c cross join @digits d cross join @digits e
where e.nr > 0
order by newid()
/rockmoose
You must unlearn what You have learnt -
I ve tried all the suggested solutons,but none of them worked..I am desperate! Once again - My table should consist of 100 new records for a field MobilePhone(of char type) and first 5 digits should be randomly choosed (should be like this: +randomno1randomno2.. etc.(example: +381564654, where '+' sign makes it char type and digits are randomly choosed.
-
create table #randomphonenumbers( nmbr char(10) primary key )
declare @digits table(nr char(1))
insert @digits(nr) select '0' union select '2' union select '4' union select '6' union select '8'
insert @digits(nr) select nr+1 from @digits -- implicit conversion
insert #randomphonenumbers( nmbr )
select top 100 '+' + a.nr+b.nr+c.nr+d.nr+e.nr + '4654'
from @digits a cross join @digits b cross join @digits c cross join @digits d cross join @digits e
where e.nr > 0
order by newid()
select * from #randomphonenumbers
drop table #randomphonenumbers
--/rockmoose
You must unlearn what You have learnt -
Oh my GOD! this actually WORKSSSSSSSS!!!!!
THANX ROCKMOOSE!!! YOU SAVED ME A LOT OF TROUBLE!!!
-
Glad to be of help
Keep practicing...
/rockmosse
You must unlearn what You have learnt -
Oh, i sure will!
And i guess I ll be posting more questions again..Nice to know you re aroundNevena
p.s. - rockmoose or rockmosse?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply