In microsoft sqlserver 7.0 i like to what is function name to generate serial numbers in a table.
For ex: in sybase we have number* which will automatically generate unique
numbers for each row.
It will be great if you could reply at the earliest
Tanks
vijay
How to generate serial numbers ? I had already tired ident. i am getting the error. Can any people who is willing to write a syntax for me.
In sybase if we use Number * function. It will automatically generates the serial numbers from 1 to n. similarly i need the same function in SQL server 7.0 so that my problem will be solved...
I am converting sybase stored procedure into sql server stored procedure that is why i am asking about that. i am struggling hard to find an answer...
please help me in this...
urs
vj
I need to put the serial numbers for results in group in SQL Server 2000. Please see below:
My input:
procedureid procname
1 A
1 B
2 A
2 B
2 C
2 D
3 A
3 B
3 C
Output I need:
procedureid procname serial_num
1 A 1
1 B 2
2 A 1
2 B 2
2 C 3
2 D 4
3 A 1
3 B 2
3 C 3
Here is my table:
create table po(
procedureid int,
procname varchar(10),
)
insert into po values (1,'A')
insert into po values (1,'B')
[Code] ....
My problem is one of my query is returing party_codes . now i also want a column which returns serial numbers along with it . the serial numbers are not stored anywhere. they should be autogenerated. My query is combining two different databases and its using union in it so i can not use count in it . Is there any other way i can acheive . for eg Now my query output is party_code ---------- R06048 R06600 R06791 (3 row(s) affected) I want it like party_code serial number ---------- ------------- R06048 1 R06600 2 R06791 3 (3 row(s) affected)
The serial number column should be auto generated in the select statement it self Is there any system rowid i can use . Please suggest
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?
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
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
I have Logs table and want to assign a serial number to the techs using the following query
Select
Date,
Case_ID,
Site,
Dept,
Tech,
Start_Time,
ROW_NUMBER () OVER (PARTITION BY Date, Site, Dept, Tech ORDER BY Start_Time) as Row_Num
FROM
Logs
Where Date = Getdate()
I get the following results.
Date Case ID Site DeptTechStart TimeRow_Num
7/28/14 10023 TartvilleMaintcAmy P.7:301
7/28/14 56789 TartvilleMaintcRem W.8:051
7/28/14 23098 TartvilleMaintcAmy P.8:352
7/28/14 70004 TartvilleMaintcAmy P.9:103
7/28/14 12708 TartvilleMaintcMag O.10:001
7/28/14 10004 TartvilleMaintcAmy P.12:304
7/28/14 40056 TartvilleServiceJoe F.7:301
7/28/14 23458 TartvilleServiceJoe F.7:552
7/28/14 69200 TartvilleServiceRus T. 7:301
Please notice the cases in Maintc department. See how Amy P.'s shift is broken by Rem W. and Mag O. But the Row Number does not recognize this, it still says Amy P's case as 2 and 4 the even though Rem's and Mag's cases were in between.
This is what I really wanted.
Date Case ID Site DeptTechStart TimeRow_Num
7/28/14 10023 TartvilleMaintcAmy P.7:301
7/28/14 56789 TartvilleMaintcRem W.8:051
7/28/14 23098 TartvilleMaintcAmy P.8:351
7/28/14 70004 TartvilleMaintcAmy P.9:102
7/28/14 12708 TartvilleMaintcMag O.10:001
7/28/14 10004 TartvilleMaintcAmy P.12:301
7/28/14 40056 TartvilleServiceJoe F.7:301
7/28/14 23458 TartvilleServiceJoe F.7:552
7/28/14 69200 TartvilleServiceRus T. 7:301
I tried many combination of columns for Partition by () and Order by () and the best I can get is the result at the top. How should I achieve it.
Dear All,I'm attempting to create a query that will transpose repeated fieldsinto a single table structure. Can anyone think of how this can be doneas I'm stumped at the minute? I'd like to do this without having tocreate a cursor due to the overheads and performance issues associatedwith cursors. The table may also include additional fields which I'mnot interested in.Serial Data is like this.............IkeyIval-----------------------------------------------RAF_EMAILJoin Bytes!RAF_FIRSTNAMEtestFirstName1RAF_LASTNAMEtestLastname1RAF_EMAILJoin Bytes!RAF_FIRSTNAMEtestFirstName2RAF_LASTNAMEtestLastname2....Transposed into table like this ..............EmailFirstnameLastname--------------------------------------------------------------------------Join Bytes!testFirstName1testLastname1Join Bytes!testFirstName2testLastname2....Any help, much appreciated ...Kind Regards,Tim-------------------------------------------------------------------------------------NOTE: these create temporary tables ....DECLARE @XML TABLE(ikey VARCHAR(200),ival VARCHAR(1000))INSERT INTO @XMLSELECT 'RAF_EMAIL', 'testemail1@hotmail.com'UNION ALL SELECT 'RAF_FIRSTNAME', 'testFirstName1'UNION ALLSELECT 'RAF_LASTNAME', 'testLastname1'UNION ALLSELECT 'RAF_EMAIL', 'testemail2@hotmail.com'UNION ALL SELECT 'RAF_FIRSTNAME', 'testFirstName2'UNION ALLSELECT 'RAF_LASTNAME', 'testLastname2'UNION ALLSELECT 'FORM_CATEGORY', 'nothing'UNION ALLSELECT 'NO_DOGS', '1'DECLARE @RESULTS(EMAIL,FIRSTNAME,LASTNAME)
View 1 Replies View RelatedHi,Please can you let me know the best solution for creating a primary key which automatically increments by 1 each time a record is added. My current Primary key is of type "Int" which increments by 1 each time, but I would like my primary key to contain "ABC" before the 1. So each time a record is added I would like to see:-ABC000001 ABC000002ABC000003Etc, EtcI am using SQL Server 2000 and creating an ASP.Net application, will I need to write code in a Stored Procedure to do this?Regards,Brett
View 4 Replies View RelatedI 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?
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 RelatedHello.
Whats the easiest way to add a column with a numeration to my table?
I want the users to have an easier life finding on which row they are
talking.
I preffer of course doing it in reporting service and not adding a
column in my dataset.
Thanks.
Hi
I have 2 columns the first column is a counter thats always counting up like a meter, the second column is a formula that calculate the difference between the counter(column one). like
Counter
Difference
1
1
3
2
6
3
9
3
11
2
13
2
17
4
25
8
The first row is taken as it is, but the second row will be difference = 3-1 the third one will be 3-6 and so on.
How can I do this in a SQL table or any other away?
Your help will be highly appreciated.
I have a field (varchar) in a list that contains numbers and letters. I want to sort this table but I have only two functions that will work to convert the values:
Val
The Val function sorts the numbers in the string, but the letters are not sorted
CStr
The CStr function sorts the letters, but the numbers are not sorted
How can I sort the numbers and letters?
I have a column within a table which is already truncated/deleted all records within (Microsoft SQL 2008). I have to now populate the column with sequential numbers up to 50,000 records arbitrary numbers (doesn't mater) up to 7 characters.
what SQL statement I need to write that will automatically polulate the newly empty table with A000001,A0000002,A0000003, or any form for that matter etc so that I can sort number the records within the table.
I have approximately 50000 records which I need to sequentially entered and I really dont want to number the column manually via hand editing.
I have a table of id numbers that I wish to mask. My thought was to create a new column for this new id number and populate it with a unique sequential value - start at 1 and go as high as needed. My problem is that I cannot recall how to populate that column with a number...
View 4 Replies View RelatedHi,
What transformations can be used to generate sequence numbers in a data flow?
Hi
I have a report, a matrix as always, were the numbers are text when I save the report to Excel. What did I do wrong or what properties do I have to change?
Kind regards
Hello All,
I have one table that contains information about invoices.
However I need to find out if invoice numbers have been used more than once.
I've tried to join the table to itself and find matching numbers, however I can't seem to get it to work properly.
Any help will be appreciated.
Thanks,
Hello,
One more question about this Custom Calendar table I'm creating. I have a column called "IsWorkdays" which indicates if the day represented by a row is a workday or not. For our purposes, I also need to create a row that accumulates those numbers by month. So, if it is the 3rd workday of the month, this column would have a 3. This is beyond my current T-SQL ability. Does anyone know how to do this?
Thanks a lot,
Andy
SQL version: 2005, Standard edition.
Hi,
If I want to automatically insert a record which has default value in a table,
how can I create the trigger?
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 RelatedI am trying to do a very small numbers table to compare A1c's against. However I am running into a issue when recursion hits the number 2.27 it starts to go out of my scope that I want with the next number being 2.27999999999999. Here is the code I'm using below. I need a Decimal(2,2) or Numeric (2,2) format with a range of 01.00 to 20.00. However every time I use Numeric or Decimal as the data type I get a error "Msg 240, Level 16, State 1, Line 5.Types don't match between the anchor and the recursive part in column "Number" of recursive query "NumberSequence"."
DECLARE @Start FLOAT , @End FLOAT ---DECIMAL(2,2) Numeric (2,2)
SELECT @Start=01.00, @End=20.00
;WITH NumberSequence( Number ) AS
(
SELECT @start as Number
UNION ALL
SELECT Number + 00.01
FROM NumberSequence
WHERE Number < @end
hi,
i am new here and interested in how to :
- execute serial dts in one single action or using vb script.
- change the status (enabled to disabled) and schedule using vb script.
Many dts make me tired when i have to change the status and schedule one by one.
regards.
I have a question on autogenerating numbers for a primary key field, "studyID," in a table€”but with a few twists.
We want studyID to be automatically generated as a 5-digit number. Additionally, we have two study sites and would like the studyIDs pertaining to the first site to begin with a 1 and StudyIDs associated with our second site to start with a 2. When we begin entering data, we will enter either a 1 or 2 in a field called, "Site." Upon entering that 1 or 2, we would like at that moment for Access to instantly autogenerate the appropriate studyID for that site and put it in the "StudyID" field. We want the very first number generated for each site to end in a 1 (10001 and 20001).
Here€™s the range of values we want our StudyIDs to be (this is to be our validation rule as well):
10001-19999 for Site 1
20001-29999 for Site 2
Your suggestions are VERY VERY WELCOME! THANKS!
In a t-sql 2012 sql update script listed below, it only works for a few records since the value of TST.dbo.LockCombination.seq only contains the value of 1 in most cases. Basically for every join listed below, there should be 5 records where each record has a distinct seq value of 1, 2, 3, 4, and 5. Thus my goal is to determine how to add the missing rows to the TST.dbo.LockCombination where there are no rows for seq values of between 2 to 5. I would like to know how to insert the missing rows and then do the following update statement. Thus can you show me the sql on how to add the rows for at least one of the missing sequence numbers?
UPDATE LKC
SET LKC.combo = lockCombo2
FROM [LockerPopulation] A
JOIN TST.dbo.School SCH ON A.schoolnumber = SCH.type
JOIN TST.dbo.Locker LKR ON SCH.schoolID = LKR.schoolID AND A.lockerNumber = LKR.number
[Code] ....
This isn't so much purely a SQL Server question as a question on ASP.NET VB technique. In particular, I have a situation where I am either inserting a NEW row for a "profile table" (name, email, etc.) or Updating an existing one. In both cases, I need to create a new row in a related table which has the identity/serial column of the parent table as the primary key for the data to be inserted into this subsidiary table (for which there may be many rows inserted, all tying back to the parent).
At the time I do the update, of course, I have the identity/serial of the "parent" so it's easy to update/insert. However, if the profile is NEW, I need to capture the identity/serial which was inserted so as to use it for the child table insert. (I remember a call to an obscure function which was -- essentially -- "give me the identity/serial of that which was just INSERTed" but I am unable to locate equivalent functionality. (I have searched various online help files for "Insert serial", "Insert identity" and the like with no results.
Hints? Mahalos in advance ... :) KevInKauai
Hey folks,
Please give me the correct function name, otherwise please ignore in sending reply. I had used all the functions like identity, ident and so on. I need while selecting a querry, i need to generate serial numbers. I dont have identity column in my table. But, i need to generate serial numbers..
In Sybase SQL Any where, we have a function called Number (*) which will in turn generate serial numbers like ex..
Select Number (*) from x
The output will be
Number(*)
1
2
3
4
5
6
and so on..
I need a equivalent function in SQL Server 7.0 in which if i do select on that particular function with a table i should return above values..
Can any one solve this issue...
Please help me in this....
Urs
VJ
Hi,
I have a table with a primary key, what I really need is something like an IDENTITY, but with the character 'X' and the last to digits of the year added on the front. Is there another way to update the field automatically like an IDENTITY would do, automatically incrementing as fields are inserted.
hi forum
how to add Serial No in "Report.rdlc". I want to add SNo. in as a extra field in Report.
SNo. ItemName Price
1 abc 10
2 xyz 20
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?
Hi,
I am getting an error when I try to insert a number typed in a text box control into a Sql database table column’s type numeric(6,2). For example: If I type 35,22 into the text box, or 35, and then I submit the form to insert the data into database, I get the follow error:
System.FormatException: Input string was not in a correct format.
Line 428: CmdInsert.Parameters.Add(New SqlParameter("@Measure", SqlDbType.decimal, "Measure"))
A piece of the SP inside Sql server:
USE market26
GO
ALTER PROC new_offer
@Offer_id bigint, @Measure numeric(6,2) = null, …
What is wrong? Why it doesn’ t accept the number typed in my text box?
Thank you,
Cesar