Random Number Generator

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


ADVERTISEMENT

Random Number Generator

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

User Defined Number Generator

May 10, 2006

Hello all!

I have a windows forms app and I'm trying to add functionality that would allow the user to enter a number into a textbox control on a setup form and then that number would autoincrement by one each time a new record on another form is inserted into the DB .

I've searched high and low and can't seem to find much information on how I can put this together. I'm sure I'll need a "numbers" table in the DB that stores the numbers and is joined with the parent table where the records are being inserted into. After that, I'm pretty much lost on how to generate the numbers in the "Numbers" table.

If anyone could assist or point me in the right direction of a resource, I would really appreciate it!!

Thanks,

Tony

View 7 Replies View Related

How To Create A Sequence Generator Number In SSIS

Mar 28, 2006

Hi,

I got 5000 rows in source and when i am sending the data to destination it has to create a sequence generator number for each row.

Can any one help me which transformation do i need to take for doing this in SSIS.













View 10 Replies View Related

Find Logic Flaw, Order Number Generator

Mar 15, 2000

This procedure has been returning duplicate numbers. (Tested with scripts that called this proc and put value in a table.)

How can it return duplicates? Does the transaction Begin/Commit not guarantee transactional consistency?


CREATE PROCEDURE sp_UpdateOrderNumber @customer int AS
DECLARE @NewOrderId int,
@nSQLError int,
@nRowCount int
BEGIN TRAN
UPDATE CUSTOMERS
SET ORDER_NUMBER=ORDER_NUMBER + 1
WHERE COMPANY_ID=@customer
SELECT@nSQLError = @@error,
@nRowCount = @@rowcount
If @nSQLError != 0 OR @nRowCount != 1 /* Check for Errors */
Begin
Rollback Tran
Return -999
End
SELECTORDER_NUMBER
FROMCUSTOMERS
WHERECOMPANY_ID=@customer
SELECT@nSQLError = @@error,
@nRowCount = @@rowcount
If @nSQLError != 0 OR @nRowCount != 1 /* Check for Errors */
Begin
Rollback Tran
Return -998
End
COMMIT TRAN

View 1 Replies View Related

Integration Services :: Auto Number Generator In SSIS

Aug 11, 2015

In SSIS, I created a flow and within that flow i need to create an automatic number generator. The flow is simple: Ole db source part, a multicast part and a flat file destination and a ole db destination. In the Ole db source there is no field that is the starting point for this generator. This automatic nr should start with 10000 and each time a row is found it should be increased by 1.

First question: is it possible to create a column in SSIS flow which is the starting point for the generator (in this case 10000) or should it be created in the source table?

Second question: where in SSIS and with what component can i create such a generator??

View 5 Replies View Related

Random Number

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

Random Number

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

Update To A Random Number?

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

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 View Related

Random Number-9 Digits Only

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

How To Gnerate A Random ID Number

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

Build Random Phone Number.

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

Generate Random And Unique Number Within Sql ?

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

Transact SQL :: Set Unique Random Number

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

Random Number Procedure Problems!

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

How To Assign Random Number To One Column For 500 Records?

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

How To Generate Random UNIQUE 13 Digit Number?

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

Transact SQL :: How To Assign Random Number Between 0 And 1 To A Record

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

SQL Server 2014 :: Generate Random Number In Range

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

Returning Random Records And NOT Similar (random Questions)

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

URGENT - Random 10 From Random 20 (2 Tbls)

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

C# Generator

May 16, 2002

The VB code was ok if you wanted it to be basic but when I was using it and trying to get it to work into my project it was quite painful. So in retrospect I have gone back to C# ... so I can have operator overloading plus smaller code anyhow ... will post here new routine ... working on one that will convert a stored procedure to a function as well...
...
I still have the old code if anyone is interested, but like I said ... it's not very robust and getting it there in VB.NET just was a bigger pain than I need at the moment ...

Edited by - onamuji on 05/23/2002 13:05:52

View 2 Replies View Related

SQL Generator

Jul 20, 2006

Hi,This may be a little OT, but I don't know exactly where else to postit. I am writing a little parser that generates valid SQL using"English like" text. I want to use this to allow non-technical users tobe able to quickly write their own queries, to search through adatabase that stores information about the sales of differentcompanies. I can provide more information for anyone who wants to help.Currently, the syntax is :Select ALL PRODUCT_FILTER from COMPANY where funcname(params) conditon.... and ITEM_DATE date_conditionWhere:product_filter specifies the product type to be included in the searchcompany specifies the company whose data is to be searchedfuncname is an aggregate functioncondition specifies the criteria for the aggregate function (i.e. aHAVING clause)date_condition specifies the criteria for the dates to be used in thesearchNote: there can be more than 1 aggregate functionA typical query then may look like this:Select all 'toys' from 'Walmart' where average_sales(100) 100000 andavaerage_cost(100,10) <= 1 and item_date between "01-Jan-00" and"01-Jan-06"I would like to know what the underlying SQL statement will look like,so that I can use this knowlege to build a generic parser that createsSQL statements from the "English like" text, using the syntax Idescribed above.

View 1 Replies View Related

Rdl Generator

Sep 19, 2007

HI all
im new to sql reporting services, im in need of a rdl (report definition language)generator
are there any resouirces out there on the net ?
if so , can u suggest some links for the same?
thanks in advance !

View 10 Replies View Related

Report Generator

Sep 1, 2004

Hello, I am new to MS SQL. I primarily used MySql, but my employer has MS SQL so i am trying to learn this. I kind of assumed that this was going to be similar to MSACCESS. My question is this. In access its easy to make forms and generate reports. Is there an "add-on" that makes it just as easy in MS SQL?

I see Crystal reports mentioned, but don't think my company would spend more money on this project.

Any help/Advice is greatly appreciated.

thanks

View 2 Replies View Related

C# Generator Script...

Nov 26, 2003

I used to have the script mentioned in this post:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=15980&SearchTerms=c#

I can't seem to find it and the link is no longer valid. Does anyone happen to have a copy of this script? I'd like to check it out again (finally starting to do regular C# programming here at work).

Thanks!

Kyle Heon
PixelMEDIA, Inc.
Senior Application Programmer, MCP
kheon@pixelmedia.com

View 6 Replies View Related

Sequence Generator

Jun 19, 2006

Hello,Since SQL Server has no sequence generator, I wrote my own. (I claimno ownership of it as it is closely modeled after earlier discussionson this topic.) I have included the sql statements below.While it works independently on its own, It seems to lock in multi-userenvironments or in nested-transactions. It s funny really: I have mymain transaction, but the sequence generator below forces anothertransaction, which I do not really care for. I cannot remove the extratransaction from the sequence generator because I would like to discardany values it retrieved, regardless of whether the main transactionsucceeded or failed.Any suggestions?--------------------------------------------------------------------------------create table my_sequence (name varchar(10), seq int identity (1, 1))godeclare @next intbegin transactionupdate my_sequence set seq = seq + 1 where name = 'abc';select @next = seq from my_sequence where name = 'abc';commit transaction---------------------------------------------------------

View 6 Replies View Related

Automatic Query Generator

Apr 6, 2007

In my final project, the biggest time consuming issue was writing a query, when there are 15 fields on the average than it is certainly not easy to write an SQL statement and run it (through code)

So I want to know, is there any automatic query generator ?
that could work like:
1) We enter all control names 1 by 1
2) we enter postfix text ( like .Trim(), .ToString() ) etc
3) Result should be automatically generated query

Is there any software/program for that ???

Infact it is very easy to develop myself, but I dont want to waste the time if such tool (most probably) already exists

View 1 Replies View Related

Query/Workload Generator

Jul 23, 2005

Hello,- Is any body aware of a random workload/query generator such as theTPC-H query generator (QGEN) ? I am looking for a query generator thattakes a schema as an input, and produces several queries.I need it mainly for performance evaluation. the generator I am lookingfor should produce SELECT queries, including joins, and not only INSERTINTO, DELETE, UPDATE queries such as the "SqlQueryGenerator" athttp://www.tucows.com/preview/297930ThanksRegards,Abdur-Rahman

View 2 Replies View Related

Stored Procedure Generator

Nov 8, 2007

I have only been into SQL Server for a few months, and written severalstored procedures. Is there a good software product available that willgenerate the SQL code for a stored procedure based on a visual interface?Any help is appreciated.

View 1 Replies View Related

Help For T-SQL Code Generator For DataMart

Jul 18, 2006

I replicated a table from DW to DM

but i filtered the table by month.

 

now i have several  tables with the same schema on the datamart

the table has five keys. what i want to do is to write a

sqlwizard code that will automatically write an update statement from the replicated

table. What the wizard will do is read the fields of the DM table then identify the keys and

generate the source code for update.

 

lets name the proc sqlwiz

exec sqlwiz (DMtable1,dwtable1)

the sp should return the desired update statement like this

                  update dmtable1  set    DM.nonkeyfield1= dw.nonkeyfield1,


                                          DM.nonkeyfield2= dw.nonkeyfield2,

                                         DM.nonkeyfield3= dw.nonkeyfield3

                                          from dwtable1 dw where

                                         dm.keyfield1=dw.keyfield1 and

                                         dm.keyfield2=dw.keyfield2

 

pls use any of the northwind table with composite pk.

my DM is sql2k5. 

 

the sp can also be used for generating update codes for vb.net

 

thanks,

joey

 

 

 

 

 

 

 

 

 

 

 

 

 

View 1 Replies View Related

Automatic Sql Script Generator For Replications

Jan 21, 2005

Hi,

I need a solution ( if this is possible) to automatically generate create scripts for my replications on our live server and put that into a file.
I dont have permission to scrip that from the Enterprise Managger, but the support have and can make a job in the live server for this.
So that would be enough for me to put a job to the live server what puts the scripts to a file.
I want to use the same replications in my test environmnet, and they are always changing and I dont want to wait for the support.

I have an idea, but I still dont try it:
If I run a trace, when i push the generate sql script button, maybe I can get the code for the generating the create script.
:confused:

Is there a simple solution for this problem, or I have to get it from the trace ?
Thanx!

Tsabi

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved