Update With Random Numbers
Jan 2, 2008
I have a table with a field called "host" and I want to replace the value of this column in each row with 'Host' followed by a random number. I have created the below function to do this:
DECLARE @Random varchar;
SELECT @Random = CONVERT(int, (20+1)*RAND())+ 1;
UPDATE AnalogLines Set [Host Name] = 'Host' + @Random
This sorta works... the problem is, the random number is determine and then the SAME random number is applied to each row. I need a different random number for every row. Any ideas?
View 3 Replies
ADVERTISEMENT
Mar 11, 2008
I have a table with a column ID of ContentID. The ID in that column is all NULLs. I need a way to change those nulls to a number. It does not matter what type of number it is as long as they are different. Can someone point me somewhere with a piece of T-SQL that I could use to do that. There are over 24000 rows so cursor change will not be very efficient.
Thanks for any help
View 6 Replies
View Related
Jun 22, 2001
I am trying to get random numbers to have a unique value for different processes, then I can identify each process. What happens is that I use rand() function without seed, so I got my random numbers, but after shutting down SQLServer and try to get again another random number after booting up, the same series of random numbers is given again and again. So if anyone knows how I can get unique values,even though reseting the server, and using random function or any other method which automatically provides unique values,I'll really appreciate it if you let me know it.
This is the function: select rand()
Alberto.
View 2 Replies
View Related
Mar 19, 2008
Hi,
I have an int column that i want to have random numbers inserted into it.
How can I create random integer numbers to insert into my column?
Thanks
Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.
View 3 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
Apr 11, 2007
Hi there... I've got an interesting one, that I can't seem to get my head around. Maybe some legend out there might be able to give me a hand...
I'm looking for a way to produce a weighted set of random numbers. I'm doing some work for a client at the moment, and they want to issue 3 random "reward cards" to their members at certain times. These are a bit like discount vouchers etc. The problem is some cards have need to have a higher frequency than the others. I guess a similar problem to baseball cards, you buy a pack of cards, you get mostly common cards, but every now and then, you get a rare card.
Here is the table setup:
CREATE TABLE [dbo].[Cards](
[CardID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Cards_CardID] DEFAULT (newid()),
[CardName] [nvarchar](50) NOT NULL,
[InsertRatio] [float] NULL,
CONSTRAINT [PK_Cards] PRIMARY KEY CLUSTERED
(
[CardID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Common 1', NULL) /* Null implies the card is a common card */
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Common 2', NULL) /* Null implies the card is a common card */
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Common 3', NULL) /* Null implies the card is a common card */
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Common 4', NULL) /* Null implies the card is a common card */
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Common 5', NULL) /* Null implies the card is a common card */
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Common 6', NULL) /* Null implies the card is a common card */
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Common 7', NULL) /* Null implies the card is a common card */
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Common 8', NULL) /* Null implies the card is a common card */
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Common 9', NULL) /* Null implies the card is a common card */
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Rare 1', 0.02) /* 1:50 ratio */
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Rare 2', 0.02) /* 1:50 ratio */
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Rare 3', 0.02) /* 1:50 ratio */
INSERT INTO [dbo].[Cards]([CardName],[InsertRatio]) VALUES('Very Rare 1', 0.005) /* 1:200 ratio */
So what I need to do, is have a Stored Proc that I can execute and it returns back 3 random rows. Now in that single run, a card can't be duplicated.
Notice the Insert Ratio column? This has the ratio of the probability, eg a 1:50 insert ratio is equal to 0.02. For the common cards, a NULL value indicates it is a common.
Eventually, this table would have about 1000 rows in it, and about 200 of those would have various ratios (eg 1:50, 1:200, 1:1000, 1:8000 etc)
Any ideas or comments?
Thanks in advance.
Leddo
Brisbane, Australia
View 6 Replies
View Related
Dec 27, 2013
s it possible to create random numbers within a range like : .28 to .57 ?
UPDATE Nop_ProductVariant
SET percent = RAND() .28 to .57
So the percent field would look like :
ProdID percent
1001 .38
1002 .29
1003 .40
etc . .
all rows having a different value
View 12 Replies
View Related
Feb 28, 2007
I'm grappling with this issue which I thought was basic VB programming; I'm trying to insert a random number (between 100 and 999) into a SQL table column (=Status_ID). This is input as part of a user submitting helpdesk requests via a APS.Net Web Form. The 'Status_ID' field is obviously not visible to the user but will help reference this Helpdesk request on the database.Here is the code:Protected Sub submitButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles submitButton.Click If Page.IsValid Then ' Define data objects Dim conn As SqlConnection Dim comm As SqlCommand ' Read the connection string from web.config Dim connectionString As String = _ ConfigurationManager.ConnectionStrings("ITNet_Students").ConnectionString ' Initialize connection conn = New SqlConnection(connectionString) ' Create command comm = New SqlCommand( _ "INSERT INTO HelpDesk (First_Name, Last_Name, StudentID, PersonalEmail," & _ "CategoryID, SubjectID, Description, StatusID) " & _ "VALUES (@First_Name, @Last_Name, @StudentID, @PersonalEmail, " & _ "@CategoryID, @SubjectID, @Description, @StatusID)", conn) ' Use randomize Randomize() Dim randomvalue As Integer ' Generate random value between 999 and 100. randomvalue = Int((900 * Rnd()) + 100) ' Add command parameters comm.Parameters.Add("@First_Name", System.Data.SqlDbType.NVarChar, 50) comm.Parameters("@First_Name").Value = fnameTextBox.Text . . . comm.Parameters.Add("@StatusID", System.Data.SqlDbType.Int) comm.Parameters("@StatusID").Value = randomvalue 'Enclose database code in Try-Catch-Finally Try ' Open connection conn.Open() ' Execute the command comm.ExecuteNonQuery() ' Reload page if the query executed successfully Response.Redirect("HelpDesk.aspx") Catch ' Display error message dbErrorMessage.Text = _ "Error submitting the help desk request! Please try again later, and/or change the entered data!" Finally 'close connection conn.Close() End Try End If End Sub----------------------------------------------------------------------------------------------------------------------I keep getting the error message under 'Catch' and the page 'HelpDesk.aspx' is not reloading; the 'comm.ExecuteNonQuery()' is not executing.Can anyone spot any inconsistencies in the declaration of the 'randomvalue' variable?P.S: this code works fine if you replace 'randomvalue' with any integer in 'comm.Parameters("@StatusID").Value = randomvalue'
View 2 Replies
View Related
Jan 19, 2007
Hello,
I need to sample data in a very large table in SQL Server 2000 (a gazillion rows of Performance Monitor statitics).
I'd like to take the top 5%, for instance, based upon a column containing random numbers.
Can anyone suggest a highly efficient method of populating a column with random numbers.
Thanks in advance.
Rod
View 10 Replies
View Related
May 20, 2015
While trying to solve a SQL challenge I found myself trying to understand what is happening when you CAST a INT to date time.
Trying to understand the results. Here are some random numbers and Castings. My question is why do they produce the datetimes they do?
SELECT CAST((1.1) AS DATETIME)
SELECT CAST((200) AS DATETIME)
SELECT CAST((15) AS DATETIME)
SELECT CAST((99.99999) AS DATETIME)
View 9 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
Aug 14, 2001
I am trying to update a bunch of phone numbers in a sql 6.5 db.
The phone numbers are in the following formate.
(aaa)-xxx-cccc
I need to update xxx to yyy. Has anyone done this before.
Please help.
Thanks in advance
Ken
View 3 Replies
View Related
Jan 4, 2007
Hello,
I use ODBC driver to perform SQLServer commands from C/C++ application.
An "INSERT INTO <table> (<column>) VALUES (NULL)" command has a random behavior in a SQL2000 Server running on WindowsXP.
The <column> in this command has 2 definitions about the NULL value :
- the NULL is accepted in the table definition, with <column> ut_oui_non NULL".
- the NULL is rejected in the type definition, with EXEC sp_addtype ut_oui_non, 'char(1)', 'NOT NULL' and a rule to check values with '0' or '1'
1/ The column definition in any explorer show the NULL from table deffinition
2/ The "INSERT INTO" is completed in SQL Query tool, used on Windows2000 and WindowsXP computers, connected to the same SQL2000 server.
3/ The "INSERT INTO" is completed in the application, running on Windows2000 with an ODBC driver to the same SQL2000 server.
4/ The "INSERT INTO" is rejected in the application, running on WindowsXP witjh an ODBC driver to the same SQL2000 server. The error 513 means that INSERT VALUES conflicts with previous rule. So only the type definition seems to be used.
But :
5/ This is a random error, and some INSERT with the same values in this column are completed.
6/ This random error seems to be discarded by using the "Use NULLs, paddings and warnings ANSI" checkbox in the ODBC driver user source configuration.
This checkbox is only use for enforcing the ANSI syntax in SQL commands, and has no known effect on type checking.
Do you know about any conflict of column NULL value between a type definition and a table definition ?
View 3 Replies
View Related
May 13, 2014
Large design flaw, I know, but we have a table with a varchar field which contains alpha - numeric values. I need to change just the numerics, which can be in an position on the field:
Sample data of the field:
Rec1: Sally Morgan 201-555-1212
Rec2: 555-4040 John Smith
Rec3: Jane Houstin 201-555-6452 ext1223
Desired result:
Rec1: Sally Morgan 999-999-9999
Rec2: 999-9999 John Smith
Rec3: Jane Houstin 999-999-9999 ext9999
There's a bunch of UDFs out there for selecting just the numerics, but I'm having trouble throwing it into a viable script for repeatable execution.
View 8 Replies
View Related
Feb 1, 2007
I have a report with a column which contains either a string such as "N/A" or a number such as 12. A user exports the report to Excel. In Excel the numbers are formatted as text.
I already tried to set the value as CDbl which returns error for the cells containing a string.
The requirement is to export the column to Excel with the numbers formatted as numbers and the strings such as "N/A' in the same column as string.
Any suggestions?
View 1 Replies
View Related
Jul 17, 2013
I have two tables. One table has empty column (ID) and I want to generate serial numbers in that column based two tables columns(LoanNum) condition.
Table A
ID LoanNum
Null 1234
Null 2345
Null 3456
Null 4567
Null 5678
Null 6789
Table B
LoanNum
1234
2345
3456
4567
5678
6789
2324
4352
4235
Id is not primary key, but should not duplicates keys, it is just a column want to generate serial number (1,2,3,4...etc) How to generate?
View 5 Replies
View Related
Feb 18, 2008
Here is my problem. I have a table with 4 columns id1,id2,id3,boxnum. Here is some sample data.
id1 id2 id3 boxnum
1 1 1
1 1 1
1 2 1
1 2 1
1 2 1
2 2 2
2 3 4
What I need is to be able to update boxnum with sequential numbers based on the unique set of values from the id columns. So my output would be this.
id1 id2 id3 boxnum
1 1 1 1
1 1 1 2
1 2 1 1
1 2 1 2
1 2 1 3
2 2 2 1
2 3 4 1
View 6 Replies
View Related
Oct 7, 2015
If Exists ( Select c.name from sys.columns c where object_id = object_id('HH835HP') and C.name = 'ID_1' )
Begin
UPDATE HH835HP
SET ID_1 =
( select ROW_NUMBER() OVER(ORDER BY CHKDTS ASC) AS ID_1 FROM HH835HP ) ;
End;
Obviously... The stuff inside the IF is wrong syntax...I mean
UPDATE HH835HP
SET ID_1 =
( select ROW_NUMBER() OVER(ORDER BY CHKDTS ASC) AS ID_1 FROM HH835HP ) ;
View 4 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
Jul 23, 2015
I have a below table,
DECLARE @TBL TABLE (ItemId INT IDENTITY(1,1), ItemName NVARCHAR(20), ParentItemName NVARCHAR(20), ItemOrder INT, ReportId INT)
INSERT INTO @TBL (ItemName, ParentItemName, ItemOrder, ReportId)
VALUES('Item1', NULL, 1, 5),('Item1-Child1', 'Item1', 0, 5),('Item1-Child2', 'Item1', 0, 5),('Item2', NULL, 2, 5),
('Item11', NULL, 1, 6),('Item12', NULL, 2, 6),('Item12-Child1', 'Item12', 0, 6),('Item13', NULL, 3, 6)
SELECT * FROM @TBL
Here,
1. for all ReportId, child items's ItemOrder = 0
2. example, for ReportId = 5, both child items ("Item1-Child1" & "Item1-Child1") of parent "Item1" has ItemOrder = 0
I need to,
1. update all child items with ascending numbers starts with 1 against each parent and each report.
2. for each different parent or different report, order by should starts with 1 again.
View 2 Replies
View Related
Sep 5, 2007
Hello All
Firstly thanks a lot Phil and Jamie on such a helpful article on "Checking to see if a record exists and if so update else insert"
Here is my question
I have about 10 tables and there respective working tables
For examples: A, B, C, D, E.... and WorkA, WorkB, WorkC....
Notes:
1) When I execute a package these work table (Work A, WorkB ...) get populated with certain rows say about 5
2) Its not that all the work table are populated on every execution.
3) Tables A, B, C... have thousands of records in it.
4) Work table is of same structure as there parent table..Like WorkA same structure as A.....
5) The table A and WorkA and as on... are linked with a KeyID
Now I want to build a SSIS package that can
1) Get the the data from these multiple tables(WorkA, WorkB...)
2) Process each row of these tables WorkA, WorkB..
3) Depending upon the KEYID of WorkA., WorkB.. etc Update a Flag colunm of table A, B...where the KeyID is equal to KeyID of Work Table
4) After updating insert that processed row of Work A, WorkB ...into Table A, B..
I can do this if I have one source table and one destination table. Here i have some say 10 randomly source tables to respective random destination .
All I could think of creating 10 different packages as adviced in Jamie's article. But I am sure there might some other alternative.
Can somebody advice me the best practice of doing this. Thanks a lot in advance
View 5 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
Jul 20, 2005
Why does M$ Query Analyzer display all numbers as positive, no matterwhether they are truly positive or negative ?I am having to cast each column to varchar to find out if there areany negative numbers being hidden from me :(I tried checking Tools/Options/Connections/Use Regional Settings bothon and off, stopping and restarting M$ Query Analyer in betwixt, butno improvement.Am I missing some other option somewhere ?
View 7 Replies
View Related
Feb 21, 2007
I have an 'ID' column. I'm up to about ID number 40000, but not all are in use, so ID 4354 might not be in any row. I want a list of all numbers which aren't in use. I want to write something like this:
select [numbers from 0 to 40000] where <number> not in (select distinct id from mytable)
but don't know how. Any clues?
View 1 Replies
View Related
Mar 27, 2007
I'm trying to write data to excel from an ssis component to a excel destination.
Even thought I'm writing numerics, every cell gets this error with a green tag:
Convert numbers stored as text to numbers
Excel Cells were all pre-formated to accounting 2 decimal, and if i manually type the exact data Im sending it formats just fine.
I'm hearing this a common problem -
On another project I was able to find a workaround for the web based version of excel, by writing this to the top of the file:
<style>.text { mso-number-format:@; } </style>
is there anything I can pre-set in excel (cells are already formated) or write to my file so that numerics are seen as numerics and not text.
Maybe some setting in my write drivers - using sql servers excel destination.
So close.. Thanks for any help or information.
View 1 Replies
View Related
Jul 20, 2005
Hi there.I need to fetch ONE random row from many. How can i get it? I try toexecute "SELECT field.table FROM table ORDER by RAND()" no effect.Thank you.
View 1 Replies
View Related
Apr 11, 2007
Hi,
i wanna fetch random data using sql query. is there any query that returns random row? Please help me.. i found some but they didnt work..
View 8 Replies
View Related
Nov 10, 2000
Hy,
I nead to make a stored procedure in order to add a random record set.
This record set is used to write in an ASP page a list which is automatically modified everytime we reload the page.
Thanks !
View 1 Replies
View Related
Nov 16, 1999
Hi,
I'm trying to use the following query to select two random records from my database. Do you have any ideas of why the recrand field will not change? I am using MS Sql server 7.. Please email mark@dtdesign.com ..Cheers Mark
SELECT TOP 2 mytable.id AS RECID, RAND(mytable.id) AS recrand
FROM mytable
ORDER BY recrand
View 3 Replies
View Related
Jul 16, 1999
Has anyone noticed that if you created DTS package and try to
change connection properties (i.e. change DSN or redirect
DTS to different server or Database), as soon you click OK
it does not save new password and hence does not work
anymore. In my case to move Database from Development to
Production server I would have to recreate all DTS packages.
Is there any way around it?
Any ideas greatly appreciated.
View 1 Replies
View Related
Dec 24, 2001
Is there a way to write an SQL statement to choose random values from a particular select statement..for example:
select * from address
I was thinking, if there was a way to use an include statement for it like:
select * from address where id IN ('generates some random values')
thanks in advance
View 2 Replies
View Related
Mar 7, 2006
So i've got to generate these queries that go from the first of a one month to the last day of a month. the user provides the starting and ending months.
What I was wondering was is there an easy way to set the last day of the month in sql without having to go in and hard code which months end on the 30 and which ones end on the 31.
View 2 Replies
View Related
Jan 1, 2005
Hello guys , I am new here,
Well, I am moving my greetings script from MS Access to MS SQL, here is what I was using in MS Access.
See this screenshot: Click Here
Well, I've imported the data from access database, but I don't know how to do the AutoNumber in MS SQL,
Also I don't know how can I add that Random thing *See the arrow in the screenshot*
Thanks in advance
View 2 Replies
View Related
