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
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
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
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.
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
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??
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
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.
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
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
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.
I just wana make a random id number based on4 digits-for examples??Thanks in AdvanceCh.Yallin
View 21 Replies View RelatedI'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
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 ?
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.
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
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!
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
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?
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 )
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 RelatedI'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.
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
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 RelatedHI 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 !
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
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
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 RelatedIn 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
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 RelatedI 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 RelatedI 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
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