Transact SQL :: How To Get 8 Digit Unique Number In Server

May 19, 2015

How to Generate the Unique 8 Digit Number in SQL SERVER.And also am trying with below methods which is not working as expected.

Methode-1:
SELECT CONVERT(VARCHAR(5),GETDATE(),112) +CONVERT(VARCHAR,DATEPART(MS,GETDATE())) AS '8DigitUniqNum'

Methode-2:
DECLARE @UniqueID uniqueidentifier
SET @UniqueID = NEWID()
SELECT LEFT(@UniqueID,8) AS '8DigitUniqNum'

View 9 Replies


ADVERTISEMENT

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 :: 4 Digit Number To Add To Table

Oct 15, 2015

I am currently working on an app and have an issue with a table in the database.  The table has 10,000 records in it and a column is added that is to use a 4 digit in sequence. The datatype for the new column is varchar since no math will ever be done on the added column.  If necessary, I can change the datatype but would prefer not to.  The 4 digit would start with 0000 at ID 1 and go to 9999 at ID 10000.  I'm thinking some type of update statement since it is updating each record but how would it be done sequentially?

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

Transact SQL :: Assign Unique Number To Records?

Nov 15, 2015

Lets say I have a table - tblProducts

View 4 Replies View Related

6 Digit Number Only

Apr 10, 2008

Hi,

I am trying to figure out and hope u guys help me.

I need a only 6 digit ID only eg

55 = 000055
405 = 000405
5544 = 005544

How i can do it in SQL server and which formula/datatype i must use?

Deaf can Do anything, except hear

View 9 Replies View Related

Datepart (to Get 2 Digit Number For The Month)

Apr 28, 2008

I want to build a string that contains today's date (without the time).

For example, if select GETDATE() returns 2008-04-28 14:18:34.180, I want to end up with either

(1) 2008-04-28
or
(2) 20080428. (I always want the month to be represented by a 2 digit number)

Is there a datepart parameter that will give me (1) in one shot? I don't think so, since datepart returns an integer and (1) is not in the form of an integer.

So, I was trying to build (2) by using the yyyy, mm, and dd parameters to extract out the appropriate parts and casting them to a string and contatentating them. However, when I do the month portion, (using mm) it gives me the integer that represents the month, and this number can be 1 or 2 digits depending on the month. I always want it to be a 2 digit number. For example, if I'm in Apr, I want to end up with "04", not "4". This would also apply to the day portion. (although the date that I'm writing this post is on the 28th so I'm not certain what GETDATE() will return on dates that are 1 digit.)

The only way I can get the single digit month to write out as a 2 digit month is by doing a LEN() function on the string and if it is a length 1, then concatenate a "0" in front of it. I started doing this and the expression became too crazy, so I wanted to first check to see if someone can come up with something cleaner.

Thanks for looking.

View 11 Replies View Related

How To Select Part Of Mobile Number From Digit

Sep 12, 2014

There is column mobile_number in my table

I have to replace with star(*) to the middle six character from contact number of 10 digit.

For example

The Contact number is 9334459875

I have to display it as follows

93******75 first two digit and last two only.

View 1 Replies View Related

How To Generate 13 Digit Number From Two Data Colu

Aug 22, 2007

Hi

I have two below datacolumns

'code'- varchar 255 (Unique number) data : chr456Umx
'Packs'- integer data : 6

Is it posible to generate 13 digit number using the above two columns,
The reason is if I run the procedure I will get same 13 digit all the time depending on the above two colums


below is the sample procedure I am using


CREATE PROCEDURE AMZSelCen
@imglink nvarchar(255)

AS

Select code as sku,
PdtBarCode as [standard-product-id],
'EAN' as [product-id-type],

--generate 13 digit number

make+' '+model+' ' +', Price for '+cast(NumPacks as varchar(8)) +' '+'Packs' as title,
make as manufacturer,'

from tablename
where ......

Advance thanks

View 3 Replies View Related

Get Unique Number In Sql Server

Nov 21, 2001

Hi,

I am new to sql server, in oracle one can get a unique number as:

create sequence my_number
increment by 1 start with 1;

select my_number.nextval from dual;

Is there any similiar mechanism in sql server? if so, how to do it?

THanks in advance,

bb

View 3 Replies View Related

Need 2 Digit Month Returned; Not 1 Digit

Sep 5, 2001

How can I return a 2 digit month into a variable for the months Jan thru September

SELECT DATEPART(mm, GETDATE()) ---> returns 9 for September.. I need 09 returned in order to properly build my target file name...

Thx,
BT

View 2 Replies View Related

SQL Server 2014 :: Getting Unique Number For Groups Of Records?

Aug 6, 2014

I'm trying to do the following and haven't been able to figure it out.

Say there's a table with these records:

Col1 Col2 Col3
a b c
a b c
a b d
e f g
e f g

I want to generate a number that represents the groups of columns like this:

Col1 Col2 Col3 MyNumber
a b c 1
a b c 1
a b d 2
e f g 3
e f g 3

So that each grouping gets its own identifier. I've tried this:

SELECT Col1, Col2, Col3
row_number() OVER (PARTITION BY Col1, Col2, Col3
ORDER BY Col1, Col2, Col3) AS MyNumber
FROM MyTable

But I get this:

Col1 Col2 Col3 MyNumber
a b c 1
a b c 2
a b d 1
e f g 1
e f g 2

View 9 Replies View Related

SQL Server 2014 :: Adding Alphabet To A Number To Make It Unique

Nov 25, 2013

create table #temp_Alpha_num (
[uniquenum] [int] Not NULL,
[Somenum] [int] Not NULL,
)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values
(1,121)

[Code] ....

For the somenum column I need to add alphabets to make them unique. for example

121a,121b,121c....121z,121aa,121ab,101a and so on...

View 4 Replies View Related

Transact SQL :: How To Select A Number Less Or Equal Than A Supplied Number

Jun 23, 2015

Got this query and I need the following result;

declare @NumberToCompareTo int
set @NumberToCompareTo = 8
declare @table table
(
number int
)
insert into @table 
select 4

[Code] ....

The query selects 4 and 5 of course. Now what I'm looking for is to retrieve the number less or equal to @NumberToCompareTo, I mean the most immediate less number than the parameter. So in this case 5

View 4 Replies View Related

Unique Transaction Number

Oct 13, 2004

As part of the credit card process I have to supply a unique transaction id.

I thought the best way to do this would be to have a file which just holds a number which I grab and increment each time some one registers. Then if everyting is alright I will write the user's details a long with the transaction id to the User file.

My question is, if two people try to register and both try to grab the next number from the file at the same time, what will happen. Will one get a record lock message, or will it wait untill the other one has finished, or could two people end up with the same number under these circumstances?

I am of course open to suggestions on the best way of approaching this, but please bare in mind that I am only using Web Matrix.

Thanks

Mark

View 1 Replies View Related

Unique Number/sequence

Jul 23, 2005

Hallo,Hot to get unique, sequential number during executionof stored procedure ?I can create table with autoincrement column,add record, get ident_current and delete recordeach time i need the number.However its not elegant i guess.best regardspluton

View 3 Replies View Related

Unique Confirmation Number

Jan 23, 2008

Hi to alll of you, i'm working in a project to save cars information, when the user who adds on the new record enter all the data, this will need to be printed with a particulary number, which needs to be unique, (lets take a passport number as an example) this generated number will takes some info from the filling fields, for example:

Cars Brand: BMW (catalog number: 120)
Cars Model: 325 (catalog number: 30)
Year: 2008 (catalog number: 18)

So the unique certification number will takes from the catalog numbers * 20 / 5 for example for cars Brand
for Cars Modelo: catalog number *3 + 15


CERTIFICATE NUMBER: 480-105

I'm not sure if this is possible.

I will realy thanks for your help in this issue.
Best Regards

Gian

View 2 Replies View Related

Generating Unique Sequence Number

Jul 19, 2000

Is there wa way to generate unique sequence numbers in SQL server?
(just like the way it is in Oracle i.e. seqeuence and then use nextval)

View 2 Replies View Related

New Unique Number In History Table

May 23, 2008

Hello,
I am using SQL Server 2005 and am having trouble with making a history table like mentioned in my earlier thread:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=102811

This is the table "People" I have created:

|PersonId (PK)|DateFrom (PK)|DateTo|PersonName|Other Attributes....

Each change to a person's attributes results in a new row formed with the same PersonId as in the row with old attributes and the Date these new attributes are valid (DateFrom). So as shown above the Primary Key is a combination of the PersonId and DateFrom as a change to a person's attributes should never happen at the same time twice.

My problem is when I want to create a new person, how do I get a new unique id? Ideally I want the a new incremented id, so that all peoples' ids are in a sequential order.

As always, thanks for the help!

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

What's This Error? (Maximum Number Of Unique SQL Exceeded)

Nov 1, 2007

My team at work has spent the past week troubleshooting performance issues experienced by users of our asp.net 2.x web application. We've got a probe running on one of the web servers that has identified a frequently occuring error that no one has seen before and I can't find anywhere online. MSSQL error "system.data.oledb.oledbcommand.executenonquery(Maximum number of unique SQL exceeded) Has anyone here ever seen this error before?The web server, application, SQL servers and databases all seem to be configured properly, but users are experiencing latency and this frequently occurring error is a mystery to us.  

View 3 Replies View Related

Re-populate A Unique Number Into Multiple Fields

Oct 14, 2004

What I need to do is re-populate a unique number into multiple fields,

Let me explain, An employee can appear in the first table only once but can be in the second table multiple times with multiple employee numbers .There is a field called TFN that is unique and we are using it to create a unique id called KRid so what I have done is created 2 tables namely TEST_TBL and TEST2_TBL . In TEST_TBL I am populating a KRid with a unique no being produced by the TFN field only once i.e 12345 being the resulting unique id number. If an employee has 2 employee numbers i.e empno 1 and empno 1000,only employee no 1 will have the unique KRid created but nothing for 1000 because the record already exists , so what has me stumped is that the TFN for employee empno 1 and the TFN for empno 1000 are the same. How do I get the KRid (12345 from empno 1) to populate empno 1000 in TEST2_TBL , The second table has all records in so I can group the second table by TFN id but how do I populate employee 1000 in the second table with the KRid 12345.


Please help!!!!! Below are how the tables are set up and an example of the result.

TABLE 1

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TEST_TBL]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TEST_TBL]
GO

CREATE TABLE [dbo].[TEST_TBL] (
[Empl_Num] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Surname] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[First_Name] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Mid_Name] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Hours_Day] [numeric](18, 2) NULL ,
[Hours_Wk] [numeric](18, 2) NULL ,
[KR_ID] [bigint] IDENTITY (1, 1) NOT NULL ,
[TFN] [char] (32) COLLATE Latin1_General_CI_AS NULL ,
[Date_Term] [datetime] NULL ,
[Empl_Type] [int] NULL ,
[Cost_Centre] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Empl_Status] [int] NULL
) ON [PRIMARY]
GO

TABLE 2

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TEST2_TBL]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TEST2_TBL]
GO

CREATE TABLE [dbo].[TEST2_TBL] (
[EmpNumber] [char] (32) COLLATE Latin1_General_CI_AS NULL ,
[TFN] [char] (32) COLLATE Latin1_General_CI_AS NULL ,
[KR_ID] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
[EmpStatus] [int] NULL ,
[EmpType] [int] NULL ,
[CommonName] [char] (32) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO

Query goes as follows for table 1:

SELECT NPE000.EmpNumber, NPET00.RecordStatus, NPE000.KR_ID, NPE000.Surname, NPE000.FirstName, NPE000.SecondName, NPE000.Class, NPE000.DateEmployed, NPE000.DateOfBirth, NPE000.HoursPerDay, NPE000.HoursPerWeek, NPE000.PassportNo, NPE000.AwardCode, NPE000.EmailPayslipTo, NPE000.Location, NPE000.Grade, NPE000.DateTerminated, NPE000.EmploymentType, NPE000.DistCode, NPE000.EmpStatus, NPET00.TaxRefNo FROM NPE000 NPE000, NPET00 NPET00 WHERE NPET00.RecordStatus = 0 and NPET00.TaxRefNo <> ' 111111111' and NPET00.TaxRefNo <> ' 000000000' AND LENGTH(NPET00.TaxRefNo) >= 9 AND LENGTH(NPE000.KR_ID) >= 0 AND NPE000.EmpNumber = NPET00.EmpNumber


Query goes as follows for table 2:

SELECT NPE000.EmpNumber, NPE000.FirstName, NPE000.Surname, NPE000.Class, NPE000.Location, NPE000.EmploymentType, NPE000.EmpStatus, NPET00.TaxRefNo, NPE000.Paypoint, NPE000.KR_ID, FROM NPE000, NPET00 WHERE Recordstatus = 0 and (EmploymentType = 1 AND EmpStatus = 1 AND NPE000.EmpNumber = NPET00.EmpNumber


From this you can see that in table 1 it will only create 1 KR_ID for only one employee number but in table 2 I am bringing through all employee records. In table 2 I can group by NPET00.TaxRefNo which will bring all NPET00.TaxRefNo's togeather. From that I would like to populate the other employee numbers with the unique KR_ID.

Example:Table 1

000001,Jackson,James,Sam,7.6,38,12345,475431212


Example:Table 2

000001,Jackson,James,Sam,7.6,38,12345,475431212
001000,Jackson,James,Sam,7.6,38,,475431212

I hope this helps

Thanks in advance

View 10 Replies View Related

To Get An Unique Sequence Number (record Locking)

Aug 27, 2007

can someone pls show me a way to get an unique sequence at below senario:

PC1 & PC2 using their own local client progam to access to Database Server at SERVER1.
In the SERVER1, there is a table SEQUENCE in a database DATABASE1.
And the table's structure of SEQUENCE are SeqType & SeqNo.
Here is the sample data:

SeqType SeqNo
Invoice 100
DeliveryOrder 200

Now, how to prevent PC1 & PC2 to get a same Invoice No. if they request the Invoice No. at the same time?
Is it possible to lock the record Invoice when i perform a SELECT statement, then i update the Invoice to 101, lastly release the lock for Invoice?

pls advise. thanks.

View 1 Replies View Related

Sequencial, Unique Number Tied To Header, Locking Q?

Sep 23, 2006

I have a situation where for a given customer, their invoices need to be sequentially numbered, without gaps.

Customer A Invoice 1,2,3,4 ...
A-1
A-2
A-3
A-4

Customer B:
B-1
B-2

etc.

The issue is 2 people creating an invoice for the same customer at the same time. Currently I don't assign the Invoice number until the user hits 'Save'. At that time I query for max(invoiceno) against the customer key and simply add 1. it's the last operation prior to saving against the backend.

If 2 users hit Save at the exact time, I'm getting the same (duplicated) Invoice Number.

What suggestions/techniques do you have to resolve this? Would "locking" of the customer record and storing the last highest invoice there play a part?

Thanks,

Peter

View 5 Replies View Related

Generating An Unique Number Within SSIS - Looking For Good Examples

Aug 17, 2007

Does anybody know how to generate a new identity value from within SSIS. Can anybody point me to a good example using a script component?

Thanks you very much!!!!
Sergio

View 2 Replies View Related

SQL Server 2014 :: Convert 6 Digit Letter To Month And Year

Feb 27, 2015

How to convert 6 digit number to mm/yyyy.

View 5 Replies View Related

Select Unique Phone Number Contacts For Same Ranked Values From The Set

Apr 30, 2015

Below is my sample data. I can't figure out how to select Unique phonenumber contacts for the same Ranked values from the set.

Basically the table is a mix of contactIDs. Some of them have duplicate phone numbers and through a separate mechanism we have ranked them.

It's easier then to pull out max(ranked) CLI_Numbers and their counterpart contactID(s). But I am also getting 2 or more records where the rank happens to be the same. I don't want that. Any one of the contactID will do for me.

The table has also same cliNumbers with different rank values, which are then correctly being picked up in the query below.

Note: ContactId is a unique value for each person in the table.
RecordID is simply RowID.

( I have attempted to populate a sample data suited for this forum - not sure how it comes out on the browser)

if object_id('tempdb..#MyData') is not null
drop table #MyData
create table #MyData
(
RecordID int,
contactID int,
forename varchar(25),
surname varchar(25),

[Code] ....

This is my query attempt

With RankedmobileDuplicateSet
as(
select cliNumber, max(ranked_value) as ranked_max_value
from #temp_UK_mobiledata
group by cliNumber)

[Code] .....

View 2 Replies View Related

Transact SQL :: Merge With Unique Identifier As PK

Jul 2, 2015

Using SQL Server 2014 i try to merge data from database [Susi] on server2 to database [Susi] on server1. Server2 is a linked server in server1. The PK of the table Core.tKontakte is uniqueidentifier with rowguidcol.

I wrote the following script and get error 206: "uniqueidentifier ist inkompatibel mit int".

    INSERT Core.tKontakte (KontaktID, AnredeID, Titel, Nachname)
    SELECT KontaktID, AnredeID, Titel, Nachname
    FROM [Susi].MSCMS.Core.tKontakte AS Client
    WHERE NOT EXISTS (SELECT KontaktID FROM Core.tKontakte AS Host WHERE Host.KontaktID = Client.KontaktID);

[Code] ....

View 8 Replies View Related

Transact SQL :: Unique Pairs Of Data?

Sep 23, 2015

In my table, I have some duplicate rows. I want to select only the unique value pairs.

So, the table/data look like this:

ColumnA  -- ColumnB
123  -- 234
234  -- 345
234  -- 345
345  -- 456

I want to create a select statement that will only return:

123  -- 234
234  -- 345
345  -- 456

In other words, the duplicate row i.e. the second 234 - 345 -- won't be included in the set my SELECT statement will return.

View 2 Replies View Related

Transact SQL :: Need Of Primary Key If Everything Can Be Achieved By Unique Key?

Oct 13, 2015

What is the need of Primary Key if everything can be achieved by Unique Key? Everything that primary key does, Unique key can also fill those things.Why primary key is required?

View 4 Replies View Related

SQL Server 2012 :: Failing On Update With Unique Index Error (Not Unique)

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

Transact SQL :: Efficient Way To Calculate Unique Values?

Jul 14, 2015

I have a data set as -

ID       Set Date          DB Date
100     Null                 07/01/15
100     07/05/15         07/02/15
100     07/10/15        07/08/15

I want to able to get 2 unique dates

1. 07/02/15 - As I want the DB date for ID - 100 when set date changed from a null value to non null value.

2. 07/08/15 - As I want the DB date for ID - 100 when a non null set date changes.

The table has such records for lot of different ID's.

View 29 Replies View Related

Transact SQL :: Insert Unique Values Only Using MERGE

Jun 2, 2015

I'm trying to use merge data from a staging table to a production table. There are a lot of duplicate values for serverName and I only want to insert one instance where there are duplicates.

How I can adapt the code I have so far to achieve this?

MERGE tblServer AS TARGET
USING tblTemp AS SOURCE
ON (TARGET.serverName = SOURCE.serverName)

WHEN MATCHED THEN
UPDATE SET TARGET.serverName = SOURCE.serverName, TARGET.serverLocation = SOURCE.serverLocation

WHEN NOT MATCHED BY TARGET THEN
INSERT (serverName, serverLocation)
VALUES (SOURCE.serverName, SOURCE.serverLocation)

WHEN NOT MATCHED BY SOURCE THEN
DELETE;

View 3 Replies View Related







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