Generate Unique Numbers
May 1, 2008
Hi
Hi
I am using sqlserver 2005 and want to generate unique numbers for all the records in a table using sql.
Something like table A has
Code:
FRUITS
-------------
Oranges
Mangoes
Apricots
....
I want to write a sql query that does something like
SELECT convert(varchar(8),getdate(),12)+'000001' my_number,
fruits
from table A
so the output is
[code]
my_number Fruits
080501000001 Oranges
080501000002 Mangoes
080501000003 Apricots
[code]
is there a way to do this without resorting to identity columns
View 3 Replies
ADVERTISEMENT
May 1, 2008
Hi
I am using sqlserver 2005 and want to generate unique numbers for all the records in a table using sql.
Something like table A has
FRUITS
-------------
Oranges
Mangoes
Apricots
....
I want to write a sql query that does something like
SELECT convert(varchar(8),getdate(),12)+'000001' my_number,
fruits
from table A
so the output is
my_number Fruits
080501000001 Oranges
080501000002 Mangoes
080501000003 Apricots
....
regards
Hrishy
View 4 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
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
Jun 19, 2015
I want to get "all combinations of the same count" for provided list of numbers.As an example, if I have a string 'NA,T1,T1a'
For this string, I want to generate following:
NA,T1,T1a
NA,T1a,T1
T1,NA,T1a
T1,T1a,NA
T1a,NA,T1
T1a,T1,NA
query to achieve the same or if the solution is already available, provide me with the links.
View 7 Replies
View Related
Aug 3, 2007
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
View 3 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
Jul 1, 2014
I have the following SQL which i want to convert to a stored procedure having dynamic SQL to generate column numbers (1 to 52) for Sale_Week.Also, I want to call this stored procedure from Excel using VBA, passing 2 parameters to stored procedure in SQL Server
e.g,
DECLARE @KPI nvarchar(MAX) = 'Sales Value with Innovation' DECLARE @Country nvarchar(MAX) = 'UK'
I want to grab the resultant pivoted table back into excel. how to do it?
USE [Database_ABC]
GO
DECLARE @KPI nvarchar(MAX) = 'Sales Value with Innovation'
DECLARE @Country nvarchar(MAX) = 'UK'
[code]...
View 1 Replies
View Related
Sep 7, 2007
I have a database of details of users that visit my site. Among other things, it saves the ipaddress and the date/time of the visit. I would like to be able to determine the unique visitors (by ipaddress) that i have on any given day. The problem though is that the data is saved as a date and a time, thus when I try and determine the unique visitors, since they are all a different time, MSSQL thinks they are all unique visitors. Any ideas how I can get around this without acctually modifying the data in the underlying table?
View 1 Replies
View Related
Oct 11, 2007
I have a procedure which updates a sequence number in a table such as the one below.
Seq Sequence_Id
------ ------------------
NextNum 1
This is the procedure ...
create procedure DBO.MIG_SYS_NEXTVAL(@sequence varchar(10), @sequence_id int)
as
begin
update mig_sys_sequences
set
@sequence_id = sequence_id = sequence_id + 1
where
seq = 'CSN'
return(@sequence_id)
end
The purpose of this is to generate a sequential number each time the procedure is called. This number would then be used in a number of different tables to allocate a unique id so that the id is unique across the different tables.
1). What is the most efficient way of allocating these unique ids? The tables that I plan to update will already be populated with data.
2). How would I call the above procedure from an UPDATE statement?
Many thanks,
Fred
View 1 Replies
View Related
Jul 1, 2014
I have the following SQL which i want to convert to a stored procedure having dynamic SQL to generate column numbers (1 to 52) for Sale_Week. Also, I want to call this stored procedure from Excel using VBA, passing 2 parameters to stored procedure in SQL Server
e.g,
DECLARE @KPI nvarchar(MAX) = 'Sales Value with Innovation' DECLARE @Country nvarchar(MAX) = 'UK'
I want to grab the resultant pivoted table back into excel. how to do it?
USE [Database_ABC]
GO
DECLARE @KPI nvarchar(MAX) = 'Sales Value with Innovation'
DECLARE @Country nvarchar(MAX) = 'UK'
SELECT [sCHAR],[sCOUNTRY],[Category],[Manufacturer],[Brand],[Description],[1],[2],
[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],
[Code] ....
View 9 Replies
View Related
Nov 6, 2007
Hi Guys,
I am trying to insert a unique code, like a voucher code, into a table in MSSQL, 10 numbers (0-9) which obviously needs to be unique.
I can code this in ASP, but I think the process could be a bit long winded, i.e. create a key - run a stored procedure to see if it is free if not loop and check again. Surely there would be a way to do this in a stored procedure in one?
So the code would look like 3928492834.
1. Generate Key in SQL
2. Check if not used in column 'kcode' if it is, regenerate and check again.
Any advice would be greatly appreciated, I know how to do this in ASP but bit of a SQL newbie!
Thanks
John
View 2 Replies
View Related
Aug 17, 2007
Is there any way to auto generate the unique primary key when inserting data in MS SQL?
there is a way to create the increasing integer as primary key, but is there any ways other than that?
thank you
View 5 Replies
View Related
Jan 25, 2008
Hy all,
I'm working on a final project for school. It is a warehouse application. For this application I will need to generate a barcode. I have read some articles online and found out that I could use the code 39 barcode font.
I just don't fully understand how to generate the unique barcode. Could someone please help me? I also would like to store the barcodes in a sql server table. Could I use reports to print the barcode?
View 2 Replies
View Related
Apr 23, 2014
I am working on an ASP.net web application which inserts new record into an underlying table.
It is actually a ConfirmationNumber and should be unique. I have used abs(checksum(newid()))
For this purpose. Is there a better way to accomplice this?
View 9 Replies
View Related
Jun 30, 2015
I need to generate unique string Id from the range AAAAA to PZZZZ. This id will be unique for each Users for e.g.
User ---- TransactionNo ---- UniqueId
--------------------------------------------
12001 --- 101 --- AAAAA
12001 ---- 102 --- AAAAB
12001 --- 103 --- AAAAC
12001 ---- 104 --- AAAAD
13001 --- 105 --- AAAAA
13001 ---- 106 --- AAAAB
13001 --- 107 --- AAAAC
13001 ---- 108 --- AAAAD
more the records of each user, unique should be the ID as displayed above.. Optimized way to generate the same...
View 3 Replies
View Related
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
Apr 20, 2015
My team is starting to implement error handling in our sprocs. One question we have is whether or not to use unique error numbers for custom errors (ie Errors we throw after doing some sort of validity check, not SQL Server errors). For example, we might check the value of a parameter and then throw an error that says "Parameter State_Date must be less than today, please retry".
We are using SQL Server 2012 and will be using the THROW statement, not RAISERROR, so we don't HAVE to put the numbers in sys.messages. Also, we are going to log the errors in a table, along with the error message, sproc name, line number, etc.
Is it useful to maintain a custom list of error numbers and messages? Or is it just as useful to use one standard error number and add a custom error message (which we can then search for in our code, or use the sproc name & line number we logged)? And if it is worth maintaining a list of numbers plus messages, should we go ahead and put them in sys.messages?
View 2 Replies
View Related
Oct 19, 2015
I am trying to achieve the below problem statement, however I am getting stuck at the looping part.
EX: We have three columns
First Name | Last Name | Mothers Name
I want to generate the username field using above columns, so lets consider, I have following data
First Name | Last Name | Mothers Name
a b cdef
a b cdfg
a b cdfj
Expected Usernames:
1: a.b
2: a.b.c
3. a.b.cd
Basically, it has to be FirstName.LastName.(incremental letters from MothersName until the name becomes unique)
View 4 Replies
View Related
May 2, 2006
Hi, all,
I have a question about adding a unique key column to an existing table.
what i trying to do is that: I have already created a table, now i wanna add a ID column to this table, and generate the values for ID column from 1 to the existing row number. How can I get this done?
Thanks a lot in advance for any guidance.
View 6 Replies
View Related
Apr 20, 2015
GENERATE 8 CHARACTER ALPHANUMERIC SEQUENCES
Requirements
• ALPHANUMERIC FORMAT – > AA00AA00………..ZZ99ZZ99
Last 8 bytes will alternate between 2 byte alpha/2 byte numeric
• Generate from Alphabets – A through Z Numbers -0 to 9
• Generate Unique Sequence (No Duplicates).
• Must Eliminate letters I and O
Output Expected
• AA00AA00………..ZZ99ZZ99
• Using 24 alphabets & 10 digits ,
24*24*10*10*24*24 = 3 317 760 000 records
Below is my Sql Function -
CREATE function [dbo].[SequenceComplexNEW]
(
@Id BIGINT
)
Returns char(8)
[Code] .....
View 9 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 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
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
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 5, 2015
This index is not unique
ix_report_history_creative_id
Msg 2601, Level 14, State 1, Procedure DFP_report_load, Line 161
Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'.
The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324).
Msg 3621, Level 0, State 0, Procedure DFP_report_load, Line 161
The statement has been terminated.
Exception in Task: Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'. The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324).
The statement has been terminated.
View 6 Replies
View Related
Sep 22, 2004
A UNIQUE INDEX must inherently impose a unique constraint and a UNIQUE CONSTRAINT is most likely implemented via a UNIQUE INDEX. So what is the difference? When you create in Enterprise Manager you must select one or the other.
View 8 Replies
View Related
Jul 20, 2005
HelloWhat should I use for better perfomance sinceunique constraint always use index ?ThanksKamil
View 5 Replies
View Related
Jun 24, 2006
What's the difference in the effect of the followings:
CREATE UNIQUE NONCLUSTERED INDEX
and
ALTER TABLE dbo.titles ADD CONSTRAINT
titleind UNIQUE NONCLUSTERED
I found there're two settings in Indexs/Keys dialog box of the management studio, Is Unique, and Type. The DDL statements above are generated by setting Is Unique to yes plus Type to Index, and just Type to Unique Key, respectively. What's the difference between them?
View 1 Replies
View Related
Mar 7, 2001
Hi everyone,
I need urgent help to resolve this issue...
As far as the performance goes which one is better..
Unique Index(col1, col2) OR Unique constraint(col1, col2) ?
Unique constraint automatically adds a unique index
and unique index takes care of uniqueness then whats the use of unique constraint ?
Which one do one use ?
thanks
sonali
View 4 Replies
View Related
Jan 20, 2006
BOL says a unique constraint is preferred over a unique index. It also states that a unique constraint creates a unique index. What then is the difference between the two, and why is a constraint preferred over the index?
View 2 Replies
View Related
Mar 26, 2008
hi team,
.Can i create umique constraint with out unique index.when i am creating a unique constraint sql creates a unique index (default) can i have only unique constraint ?
View 12 Replies
View Related
