How To Generate Random Number For The Value In Th
Aug 2, 2007
How to create random number for the value in other colum. Please help and urgent
I have a table with 4 column ( ID, Ori_Quantity, Rand_Quantity, Location)
If Ori_quantity < 5000 then
Rand_quanty as qty = I want Random number within 100
Else If Ori_quantity = 0 or < 10 then
Rand_quanty as qty = I want Random number within 7
End if
From tblname where Location = 'DAS'
I have around 2500 field. So when I run the query I expect the result should be
like below
Ori_Quantity, Rand_Quantity
------------ -------------
4000 90
2986 57
2500 89
I don't want to pass any parameter and I am new. Please help
View 7 Replies
ADVERTISEMENT
Dec 11, 2007
I need to generate a random 10 digit alphanumeric string that is also unique within a table. My application will be calling a stored procedure to insert this number into the table. This number will be associated with a id from another table. Is it better to generate the random number within sql (and perform the lookup at the same time), then just pass the number back to the calling application ?
If the calling application generates the number, it will also need to make a call to check if its unique. So im thinking it would be best to simply have sql generate this random number, check the number against the table and then insert the new record.
thoughts ?
View 5 Replies
View Related
Aug 22, 2007
Hi
I have a sql procedure. I need to create UNIQUE random 13 digit number to use for barcode.
How do I generate 13 digit UNIQUE random in sql procedure?
Advance thanks
View 20 Replies
View Related
Oct 3, 2015
I have a function which generate random number in range :
CREATE FUNCTION Func_Rand
(
@MAX BIGINT ,
@UNIQID UNIQUEIDENTIFIER
)
RETURNS BIGINT
AS
BEGIN
RETURN (ABS(CHECKSUM(@UNIQID)) % @MAX)+1
END
GO
If you run this script you always get result between 1 and 4
SELECT dbo.Func_Rand(4, NEWID())
BUT, in this script sometimes have no result !!!!!!! why??
SELECT 'aa'
WHERE dbo.Func_Rand(4, NEWID()) IN ( 1, 2, 3, 4 )
View 2 Replies
View Related
Dec 12, 2006
Hi, how do I use the Rand Function to generate a Random integer value between 1 and 20. Any help will be appreciated.
Thanks
View 1 Replies
View Related
Jul 8, 2007
Dear All,
I have some problems with the assignment from my professor.
Suppose there are two tables storing first names and last names, one table with column of first name and gender, the other with last name.
how to write stored procedures to get a random first name + random last name for Males or Female. like you got a list of all M & F first name and last name, then you try to match first name with any other last name (I believe this is random).
and the task is to write a script so each time when a female or male full name is need, it will return a random name for the requested gender.
I am totally struck and no idea how to deal with it, in fact I am just a beginning in SQL server, and I know there is a stored procedure to generate random number, however, I can't see the use of it here,,,,:eek:
Could anyone help me out ? thanks very much !!!:beer:
View 2 Replies
View Related
Aug 20, 2013
I need to generate random strings each of length 7 characters and update it in a table that contains employee data.For example. There is table called as Employee. It has Id, FirstName, LastName & Email as the columns. I need to mask the real names. So i have to generate random characters between a-z unique for individual records and update it in the table.
Input table
IDFirstNameLastNameEmail
1GlenJacksontest1@email.com
2ChristopherMcClaintest2@email.com
3Carol Kholertest4@email.com
4JamesDesototest5@email.com
Output table should be:
IDFirstNameLastNameEmail
1KhjkltgErstlkjtest1@email.com
2AsdlkfuAlsdkjftest2@email.com
3AlsdkflAldkjfltest4@email.com
4BasdfweAsldkfjtest5@email.com
I went through some websites for generating random characters. These provide a 7 or 8 character random letters. URL...But i do need to update 2 columns of an entire table with random characters.
View 4 Replies
View Related
Jul 20, 2005
I need to generate a random whole number between 0 and 999 to add toadd to every record in a table. The number needs to be different foreach record in the table.How would I do this all within TSQL?
View 1 Replies
View Related
Oct 13, 2005
hi
I have a field call: password, I want to generates some random chars as
default password for users (so user got something to login, and update
later).
Current Solution I have:
I made myself a trigger and it does generate password on insert, but
the problem with that is I have to allow NULL on that field. I
don't want NULL to be allowed, and if I don't allow null... sql server
won't allow me to insert a record (when leaving password field empty).
Reason i don't wanna allow NULL on that field, is because there may be
more applicaions using the same field from the same database.
Just in case if the other developer gets sloopy, I don't want other
application(s) allow user put to enter a null or leave it blank on my
password field.
My question is:
1) can I put a function or some sort at the "Default" field under "Design Table", if so...how?
2) any suggestions for a better implementation?
3) if (1) doesn't work, what other options would you suggest other then the current trigger i'm using?
TIA
View 5 Replies
View Related
Apr 15, 2002
Generates a true random number from xxx to zzz. Uses a table variable so it all depends on the boundaries you specify how long it will take.
create procedure random_number
@lower int = 0,
@upper int = 256,
@number int = null output
as
set nocount on
declare @numbers table (number_id int identity(1,1), value int)
if @lower > @upper
begin
set @number = @lower
set @lower = @upper
set @upper = @number
end
set @number = rand((datepart(mm, getdate()) * 100000) + (datepart(ss, getdate()) * 1000) + datepart(ms, getdate()))
while (select count(*)
from @numbers) < (@upper - @lower + 1)
begin
insert into @numbers (value)
select (@upper - @lower + 1) * rand() + @lower
end
select @number = value
from @numbers
order by newid()
go
View 16 Replies
View Related
Oct 8, 2005
I need to update an integer column in my table with a random number. Here's what I've done so far (with the table name and column names changed):
UPDATE dbo.MyTable
SET Column1 = RAND() * 1000000
My question is, how come the values in my column all have the same value? I thought the RAND function gives out a random number? How can I update my table to generate random numbers?
Thanks in advance.
View 3 Replies
View Related
Nov 27, 2004
Hi,
How can I update my users table and set the password field to a random number or string.
eg.
update tbl_users set password='a way to generate a random number say between 2000 and 100000 for example'
even better would be a randomly generated alphanumeric string.
All help much appreciated.
Cheers,
RG
View 2 Replies
View Related
Feb 23, 2005
i have an application that uses a table for login access, for security reason i need to get a random numbers in this table daily. table consist of just two columns, userid and password. This table will be printed out on the web for users to get valid username and pw daily.
I m thinking about a job or dts package that will run once daily with a sql script to generate random number (referable 5 digit-letters and numbers), delete the table and re-create the table with same set column names and insert the random values in username and pw column.
Will appreciate help on this
Thanks
View 1 Replies
View Related
Mar 25, 2008
Hi,
I need to generate random numbers that will always contain 9 digits.
How can I do that?
Thanks
Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.
View 1 Replies
View Related
Jun 9, 2007
I just wana make a random id number based on4 digits-for examples??Thanks in AdvanceCh.Yallin
View 21 Replies
View Related
Sep 28, 2007
In a Name table, I need to generate unique 6 digit random numbers in a field called UniqueID for all records that have the ID field populated.
I will need to run this script periodically. It is critical that any prevoiusly assigned UniqueIDs do not change and only fields that have an empty UniqueID field are updated. I need to preserve the historical mapping of the existing IDs to the ongoing assignment of UniqueIDs.
Regards,
Polar Bear
View 3 Replies
View Related
Jan 15, 2005
I'm building a test database and I need to randomly create phone numbers in the format xxxxxxxxxx
I have 5000 contacts and need to generate fake phone numbers for them. anyone know how I can write an update query to do this?
ScAndal
View 4 Replies
View Related
Dec 1, 2015
Lets say we have a table (tblProducts)
ID  Item           RandomNumber
-------------------------------------------
1Â Â Â JEANSÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 1234567
2Â Â Â SHIRTÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 72813550
3Â Â Â HOODÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Null
4Â Â Â TROUSERÂ Â Â Â Â Â Â Â Â Â Â Â Â 72191839
5Â Â Â BLAZERÂ Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â 0
I want to perform a query so that SQL should look for RandomNumber Values and set a Unique Random Number Where RandomNumber Value is Null or 0.So I have got a solution as one of the MSDN Member shared the below query
select id,item,RandomNumber=Case when RandomNumber=0 then (select floor(rand()*100000000-1))
when RandomNumber is null then (select floor(rand()*100000000-1))
else RandomNumber end from tblProducts
So, can you all confirm me, that performing this query ensures that if a Value is assigned to one of the Item in RandomNumber Column, that value will not be assignend to any other Item in RandoNumberColumn.
View 15 Replies
View Related
Sep 7, 2006
Hey Guys, I have a quick stored
procedure question for you. I created a procedure that returns a randomly
generated number.
I know there is a built in
Rand() function but I need to be able to
specify the range. Anyways, it€™s returning multiple numbers (1 for each row in
the tempTable)
But I just want one number returned.
I tried using MAX( ) but that€™s not a good way of doing it.
My question is, how do I return just
one record or number?
Thanks
Fellas,
Chris
ALTER PROCEDURE
dbo.spx_GetRandomNum
AS
DECLARE @Start_Num int
DECLARE @End_Num int
DECLARE @ReturnValue
int
DECLARE @RecNum
int
DECLARE @CurrentRec
int
DECLARE @MyCount
int
SET @Start_Num=1
SET
@End_Num=100
CREATE TABLE #tblTemp (CurrentRecNo
int IDENTITY(1,1), RandomNumber int null)
SET @MyCount = ( @End_Num -
@Start_Num ) + 1
SET @CurrentRec = 1
WHILE @CurrentRec <= @MyCount
BEGIN
SET @RecNum
= CAST((RAND() * @MyCount) AS int) + @Start_Num
INSERT INTO
#tblTemp (RandomNumber) VALUES (@RecNum)
SET
@CurrentRec = @CurrentRec + 1
END
SET
@ReturnValue = (SELECT RandomNumber FROM
#tblTemp)
DROP TABLE #tblTemp
RETURN
@ReturnValue
GO
View 4 Replies
View Related
Aug 4, 2005
I try to set up a testing sample table which contain one integer project_ID field for table Sample around 500 records, and want the project_ID to be random number within 1 to 99, how to implement script todo it?
thanks!
View 1 Replies
View Related
Nov 17, 2015
I need to assign a random number between 0 and 1 to all eligible cases of cancer.Â
I have a file of records like:
patientid
tumid
site
How can I assign a random number to each record?
View 10 Replies
View Related
Mar 15, 2005
Hi,
I have written the following StoredProcedure
Code:
create Procedure spCreateQuestion(
@QuestionName varchar(30)
)
as
declare @newAnswerId int
declare @newQuestionId int
set @QuestionName='New Question'
BEGIN TRANSACTION Q1
--Creates New QuestionId with AnswerId 0
INSERT INTO Questions(QuestionId,Name,AnswerId)
SELECT 1 + COALESCE(MAX(QuestionId), 0),RTRIM(@QuestionName),0
FROM Questions
--QuestionId just now created
SELECT @newQuestionId=QuestionId FROM Questions WHERE Name=@QuestionName
BEGIN TRANSACTION QA1
--Create an AnswerId
INSERT INTO Answers(AnswerId)
SELECT 1 + COALESCE(MAX(AnswerId), 0)
FROM Answers
--AnswerId just now created(I hope not the best way to do like this)
SELECT @newAnswerId=MAX(AnswerId) from Answers --is it the best way to call statement like this or any other way better than this
--update Questions Table with this new Answerid
UPDATE Questions
set
AnswerId=@newAnswerId
where QuestionId=@newQuestionId
COMMIT TRANSACTION QA1
COMMIT TRANSACTION Q1
I think the second Transaction is not locking the table.so some how i should be
able to get the newly create AnswerId
i can't use the identity column in my tables
Can some one please have a look at it and suggest me how do we go about it..
View 8 Replies
View Related
Oct 4, 2001
Hello all,
Is there any way to force Autonum to generate a number before an entire record is created? Some of my forms will not work because it needs a number already listed in its index (which uses Autonum) and cannot add to the table until it is created.I really need it to have a number ready and waiting upon the last record's completion.
View 1 Replies
View Related
Jul 30, 2002
Here is the example
state
-----
NY
NJ
CA
NY
NC
NJ
CA
IL
CA
Can we generate a result like this with select statement
state No
----- ---
CA 1
CA 2
CA 3
NJ 1
NJ 2
NY 1
NY 2
IL 1
NC 1
View 1 Replies
View Related
Mar 28, 2008
Hi all
anbody can help me writing sql code for this. All i need is to generate sequence basing on id_no
Ex: if ID=ABC(twice) in seq_col as abc --1
abc ---2
Tables which I have
Uniques_No ID_NO SEQ
---------------------------------------------
1 ABC
2 ABC
3 ABC
4 BBC
5 BBC
Expected results as below :
------------------
Uniques_No ID_NO SEQ
---------------------------------------------
1 ABC 1
2 ABC 2
3 ABC 3
4 BBC 1
5 BBC 2
Thanks in advance
View 4 Replies
View Related
May 26, 2008
Hi all
i wants to generate linear sequence number like 1,2,3,.............1000000
,are there any function like NEWID() ( this return unique guide, i want to get integer)
i want to used this generated number inside the SQL query
thanks
IndikaD (Virtusa Cop)
View 12 Replies
View Related
May 22, 2006
Hi, in Access, I can use an Auto-Increment number for my primary key field. May I know how do I do that in SQL Express?
In addition, is there any tutorial on how to use SQL Express to generate customised unique numbers (such as membership number, Customer ID such as A001 where A is based on the customer's name while 001 is due to the fact that the customer is the first among those with names starting with A)?
Thanks a lot.
View 2 Replies
View Related
Sep 3, 2014
I'm trying to do a simple insert into a table, something like this:
insert into sometable (ID, somecolumn)
select 'Task-ID', somevalue from SomeOtherTable
where something = 'someothervalue'
(or something to that effect)
So, the SELECT would generate something that looks like this:
ID somecolumn
-- ----------
Task-ID somevalue1
Task-ID somevalue2
Task-ID somevalue3
(etc.)
Here's where my problem comes in: ID is a PK, and needs to be unique. What I need it to do is this:
ID somecolumn
-- ----------
Task-ID.1 somevalue1
Task-ID.2 somevalue2
Task-ID.3 somevalue3
(etc.)
What I don't know is, how do I programatically generate the number sequence? Note: I do not have admin rights to the table, i.e. I cannot just change a column to IDENTITY.
Also the 'Task-ID' must remain part of the ID; in other words, I can't just generate a GUID, and it needs to be easily identifiable.
What I'm hoping to do is rewrite my SQL like this:
insert into sometable (ID, somecolumn)
select 'Task-ID.' + (generated seq #), somevalue from SomeOtherTable
where something = 'someothervalue'
Is there an easy way to do this?
View 9 Replies
View Related
Aug 22, 2007
Hi
I have two below datacolumns
'code'- varchar 255 (Unique number) data : chr456Umx
'Packs'- integer data : 6
Is it posible to generate 13 digit number using the above two columns,
The reason is if I run the procedure I will get same 13 digit all the time depending on the above two colums
below is the sample procedure I am using
CREATE PROCEDURE AMZSelCen
@imglink nvarchar(255)
AS
Select code as sku,
PdtBarCode as [standard-product-id],
'EAN' as [product-id-type],
--generate 13 digit number
make+' '+model+' ' +', Price for '+cast(NumPacks as varchar(8)) +' '+'Packs' as title,
make as manufacturer,'
from tablename
where ......
Advance thanks
View 3 Replies
View Related
May 22, 2015
I need to create a script that adds an incrementing suffix to two columns, but restarts based on the value of another column. I found a similar question in the SQL Server 2000 forum, but it doesn't quite fit and also I'm working with SQL Server 2008 R2. The code below both creates a table with test data and tries to carry out the task. If you run this, you will see that the VISITNUM column has a value of UNS in row 4, UNS.1 in row 5 and UNS.2 in row 6. In row 7 it's V200, then in rows 8 and 9 it's UNS.3 for both. The same suffix gets applied to the VISIT column, but of course if I can solve this for VISITNUM then adding the suffix to VIST as well will be easy.
What I need is for row 8 to have UNS and row 9 to have UNS.1. In other words, any time the VISITNUM is UNS several times in a row, I need to add that ".X" suffix, but if a row has something other than UNS, I need to start over again the next time it's UNS again.
CREATE TABLE #testing(
KitID varchar(20),
SubjID varchar(20),
VISIT varchar(60),
VISITNUM varchar(20),
[code]....
View 8 Replies
View Related
Jul 20, 2005
Hi,I need to extract randomly 5 records from the table "Questions". Now I useSELECT TOP 5 FROM Questions ORDERBY NEWID()And it works. The problem is that I need an additional thing: if SQLextracts record with ID=4, then it should not extract record with ID=9,because they are similar. I mean, I'd like something to tell SQL that if itextracts some questions, then it SHOULD NOT extract other ones.How can I do it?Thanks!Luke
View 1 Replies
View Related
Oct 14, 2004
I'm using ASP and SQL Serv 2000. What I need to get from 2 tables (company & customers) is random 10 customers from random 20 comp.
Anyone got an idea how to do this??? I've spent 2 days trying to get stored proc. or T-SQL to work, but nothing good came out of it. I can get 1 comp and 10 cust, but not a grouped list of 20 comp. w/ 10 cust. each.
Help is greatly appreciated.
View 1 Replies
View Related
Mar 3, 2008
Hello everybody,I was configuring a SqlDataSource control using SQL Authentication mode.I first added a database file (testdb.mdf) through Solution Explorer-Add New Items. Then through Database Explorer I created a table named "info"Then while configuring the SqlDataSource control I used the SQL Authentication mode and attached the "testdb.mdf" database file.Test Connection showed success. But when I hit the Ok button of the wizard it displayed the following error message:Failed to generate a user instance of SQL Server. Only an integrated connection can generate a user instance.While configuring the SqlDataSource control I clicked "New Connection". Under Data Source section I tried both Microsoft SQL Server and Microsoft SQL Server Database File. And in both the cases I attached a databese file(testdb.mdf). Plz enlighten me on this.Thanks and Regards,Sankar.
View 1 Replies
View Related
