need unique value - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

need unique value

Author

Topic

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-02-16 : 11:41:01

I have a table to which i need to add a column that calculates a non repeating, sequential would even be ok, for each row. is there an easy way to drop a value like that into each row?

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-16 : 11:43:19

Sequential in relation to what?

Post the DDL of the table and Sample Data

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-02-16 : 12:04:46

sequential in relation to the number 412 or 7 or 33 or 19 or anything, but the string cannot exceen 6 characters.

I hate requests for "sample data" because the formatting consistently gets FUBAR between the cut and the paste on this board

If anyone can give me a clue as to how the hell you paste that data in here without it going berserk, i'd love to hear it.

/****** Object: Table [dbo].[persondatatoconvert] Script Date: 2/16/2012 10:57:20 AM ******/
USE [DouglasMNI];
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE TABLE [dbo].[persondatatoconvert] (
[PersonID] int IDENTITY(1, 1) NOT NULL,
[PersonTypeID] int NOT NULL,
[FormTypeID] int NOT NULL,
[FormID] int NOT NULL,
[FNAME] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MNAME] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LNAME] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Suffix] varchar(5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AddressLine1] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AddressLine2] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[City] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[State] varchar(5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Zip] varchar(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[WorkPlace] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FaxPhone] varchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BeeperPhone] varchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CellPhone] varchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[WorkPhone] varchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DOB] datetime NULL,
[CensusTract] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Height] varchar(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Weight] varchar(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[HairColorID] int NULL,
[EyeColorID] int NULL,
[JuvenileAdultID] int NULL,
[SSN] varchar(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[RaceID] int NULL,
[SexID] int NULL,
[DriversLicenseNumber] varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DLState] varchar(5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DLExpireDate] datetime NULL,
[Email] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[HomePhone] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DLCountry] varchar(5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IMPORTED_DATE] datetime NULL,
[Sex] nchar(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Race] nchar(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[eyes] nchar(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[hair] nchar(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MNInoMID] nchar(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MNInoFRONT] nchar(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MNInoBACK] nchar(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MNINO] varchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL)
ON [PRIMARY];
GO

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-16 : 12:07:13

You need to use [ code] [ /code] tags (without the space)

>> sequential in relation to the number 412 or 7 or 33 or 19 or anything,

And what the heck does that even mean

I meant in relation to what Column?

We NEED to know what to ORDER BY

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-16 : 12:07:52

SEXID int

No that's just silly

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-02-16 : 12:19:22

Sorry, my frustration tolerance is wicked low today.

Lets just make it simple. Lets not make the value sequential, let's make it random and six chars in length, non repeating.

I have a function that does some crazy voodoo stuff where it reads the mac ID of my PC and generates some uniquekey by calculating a boolean expression between my mac id, my guid, and the gravitation pull of the moon at the moment, but that number is 25 chars in length. Could i use that function but pull a unique susbstring out of that result or is there an easier way to gen a random string into a column?

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-16 : 12:21:41

Whatever..the point is you need to understand that your data should be ordered in some way

But since you don't seem to care, do this

ALTER TABLE Persondatatoconvert ADD bogusCol int IDENTITY(1,1)
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-02-16 : 14:22:16

Thank you. This works up to the point where my identity seed exceeds 6 digits. At the point where it becomes 7 digits, i need to alter the value of another column. I'm intending to use a CE to make this happen, but my syntax in the CE is flawed.

SELECT MNIfront, Mniid
CASE mniid
WHEN MNIid <999999
THEN UPDATE persondatatoCONVERT SET mnifront = 'DCSO05MNI',
WHEN MNIid>999999
THEN UPDATE persondatatoconvert SET mnifront = 'DCSO06MNI',
ELSE MNIfront
END

corrections are appreciated.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-02-16 : 15:00:38

quote:
Originally posted by WJHamel

Lets just make it simple. Lets not make the value sequential, let's make it random and six chars in length, non repeating.

Now that's harder. A sequential integer (either ordered by some column in the table or ordered randomly if you like) is easy. Non-repeating but non-sequential random numbers are hard.

If sequential, have a read up on the ROW_NUMBER function.

--
Gail Shaw
SQL Server MVP

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-02-16 : 15:02:54

quote:
Originally posted by WJHamel

SELECT MNIfront, Mniid
CASE mniid
WHEN MNIid <999999
THEN UPDATE persondatatoCONVERT SET mnifront = 'DCSO05MNI',
WHEN MNIid>999999
THEN UPDATE persondatatoconvert SET mnifront = 'DCSO06MNI',
ELSE MNIfront
END

If you're after an update statement...

Update persondatatoCONVERT 
  SET mnifront = CASE WHEN MNIid <999999 THEN 'DCSO05MNI' 
                      WHEN MNIid>999999 THEN 'DCSO06MNI'
                      ELSE <Equal to 999999, what to do here> 
                 END

--
Gail Shaw
SQL Server MVP

WJHamel
Aged Yak Warrior

651 Posts

Posted - 2012-02-16 : 15:06:28

that is what i was after. thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources