Generate detailed Primary Key - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Generate detailed Primary Key

Author

Topic

p.shaw3@ukonline.co.uk
Posting Yak Master

103 Posts

Posted - 2005-10-28 : 04:37:50

Is there a way to generate a unique Primary Key such as PRS001, then PRS002 and so on, instead of simply 001 and 002.

Thanks.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-28 : 05:09:56

1 You can have two columns one having the value PRS and other with incremental values
2 You can simply have incremental values like 001, 002, etc and when selecting or showing data append 'PRS'
3 Take substring starts at 4th character, increase it by one and append them

Madhivanan

Failing to plan is Planning to fail

p.shaw3@ukonline.co.uk
Posting Yak Master

103 Posts

Posted - 2005-10-28 : 05:31:57

Hi Madhivanan,
Is there any way you could show me an example of No.3 please.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-28 : 05:43:54

declare @s varchar(10)
set @s='PRS001'
select left(@s,3)+right('00'+cast(max(substring(@s,4,len(@s))+1) as varchar),3)

Madhivanan

Failing to plan is Planning to fail

activecrypt
Posting Yak Master

165 Posts

Posted - 2005-10-28 : 05:50:11

Hi,

declare @vno varchar(4)
declare @no int
if exists (select vno from testtbl where vno is not null)
begin
set @vno = (select right(max(vno),6) from testtbl)
set @vno = @vno + 1
if len(@vno)= 1
begin
insert into testtbl values('prs00'+convert(varchar(4),@vno))
end

end
else
begin
insert into testtbl values ('PRS001')
end

-----------------------------------------------------------
MSSQL Server encryption software http://www.activecrypt.com

p.shaw3@ukonline.co.uk
Posting Yak Master

103 Posts

Posted - 2005-10-28 : 05:55:26

Thanks very much guys. While I'm here, I have another question that you may be able to help me with. I need to rename an .xls file that is part of a DTS package using an ActiveX script. I want to use vbScript if poss. Can you give me an idea as to how this can be done?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-28 : 05:55:28

>>set @vno = (select right(max(vno),6) from testtbl)

You will get error if subquery returns more than 1 value
So its better to use this

Select @vno = right(max(vno),6) from testtbl

Madhivanan

Failing to plan is Planning to fail

activecrypt
Posting Yak Master

165 Posts

Posted - 2005-10-28 : 06:07:28

Hi,
refer [url]www.sqldts.com[/url]

Mr.Madhivanan
>> Select @vno = right(max(vno),6) from testtbl
thanx to direct me on this , but how select max(col) from tbl can return more than one value ?

HTH

-----------------------------------------------------------
MSSQL Server encryption software http://www.activecrypt.com

activecrypt
Posting Yak Master

165 Posts

Posted - 2005-10-28 : 06:22:31

Hi,
this is the complete script and i test it for value of * PRS999 * it works without any error

quote:


create table testtbl
(
vno char(9)
)


declare @vno varchar(4)
declare @no int
if exists (select vno from testtbl where vno is not null) 
begin
	set @vno = (select right(max(vno),6) from testtbl)
	set @vno = @vno + 1
	if len(@vno)= 1 
	begin
		insert into testtbl values('prs00'+convert(varchar(4),@vno))
	end
	if len(@vno)= 2 
	begin
		insert into testtbl values('prs0'+convert(varchar(4),@vno))
	end
	if len(@vno)= 3 
	begin
		insert into testtbl values('prs'+convert(varchar(4),@vno))
	end
end
else
begin
	insert into testtbl values ('PRS001')	
end

select * from testtbl

-----------------------------------------------------------
MSSQL Server encryption software http://www.activecrypt.com

p.shaw3@ukonline.co.uk
Posting Yak Master

103 Posts

Posted - 2005-10-28 : 07:02:01

Thanks for all your help. I went to SQLDTS.com and found what I wanted for the ActiveX. Just one problem. The variable I am using to act as the new name for the file comes from Date(). I can't get the new filename to show this.
For example:
Option Explicit
Function Main()

Dim oFSO
Dim sSourceFile
Dim sDestinationFile
Dim FileNewName

FileNewName = Date()

Set oFSO = CreateObject("Scripting.FileSystemObject")

sSourceFile = "\\amcatsql\e$\Monitor\Altrincham.xls"
sDestinationFile = "\\amcatsql\e$\Monitor\FileNewName.xls"

oFSO.MoveFile sSourceFile, sDestinationFile

' Clean Up
Set oFSO = Nothing

How do I get the new file name to show "28/10/05.xls" instead of "FileNewName.xls"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-28 : 07:06:36

Like this

declare @sDestinationFile varchar(100)
set @sDestinationFile ='\\amcatsql\e$\Monitor\'+convert(varchar,getdate(),101)+'.xls'
select @sDestinationFile

Madhivanan

Failing to plan is Planning to fail

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2005-10-28 : 07:16:07

Just for fun, you can do it also this way.


SET NOCOUNT ON
DROP TABLE testtbl
CREATE TABLE testtbl
(
vno VARchar(9)
)
INSERT INTO testtbl VALUES ('PRS001')	
INSERT INTO testtbl VALUES ('PRS002')	
INSERT INTO testtbl VALUES ('PRS010')	
INSERT INTO testtbl VALUES ('PRS998')	

INSERT INTO testtbl
 SELECT 'PRS' + REPLACE(STR(MAX(RIGHT(vno,3))+1,3), ' ', '0') FROM testtbl

SELECT *
  FROM testtbl

SET NOCOUNT OFF

vno       
--------- 
PRS001
PRS002
PRS010
PRS998
PRS999

However, I would really consider Madhivanan's 1. or 2. suggestion.

--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon geblogged? http://www.insidesql.de/blogs
Ich unterstütze PASS Deutschland e.V. (http://www.sqlpass.de)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-28 : 07:18:39

>>However, I would really consider Madhivanan's 1. or 2. suggestion.

Thanks Frank. Nice to see you here after a long time

Madhivanan

Failing to plan is Planning to fail

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2005-10-28 : 07:20:37

--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon geblogged? http://www.insidesql.de/blogs
Ich unterstütze PASS Deutschland e.V. (http://www.sqlpass.de)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-16 : 14:31:12

Also look at this topic
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=67923

Peter Larsson
Helsingborg, Sweden

cgig
Starting Member

2 Posts

Posted - 2009-05-18 : 15:03:33

quote:

Originally posted by activecrypt

Hi,
this is the complete script and i test it for value of * PRS999 * it works without any error

quote:


create table testtbl
(
vno char(9)
)


declare @vno varchar(4)
declare @no int
if exists (select vno from testtbl where vno is not null) 
begin
	set @vno = (select right(max(vno),6) from testtbl)
	set @vno = @vno + 1
	if len(@vno)= 1 
	begin
		insert into testtbl values('prs00'+convert(varchar(4),@vno))
	end
	if len(@vno)= 2 
	begin
		insert into testtbl values('prs0'+convert(varchar(4),@vno))
	end
	if len(@vno)= 3 
	begin
		insert into testtbl values('prs'+convert(varchar(4),@vno))
	end
end
else
begin
	insert into testtbl values ('PRS001')	
end

select * from testtbl

-----------------------------------------------------------
MSSQL Server encryption software http://www.activecrypt.com

I have a similar situation. The values of the primary keys are someting like this: W.9100
Where
W. is the projecttype and is defined by the user when they make a project. There is also T.
9 is the last digit of the current year. It has to be incremented automatically every year on January 1st.
100 is the project number. For project type T., C., en H., this number has the range 0 to 250. For Project type W, this number has range 250 to 1000

I am struggling with this one for days. Can anyone please help?

davidredden1973
Starting Member

3 Posts

Posted - 2009-08-21 : 17:53:49

Would this possibly work with a scenarios as such:
I need to create a url address.
So in one new column labeled URL1 I would put in the entire url address minus the field that will be the id= portion of the URL address.
Then, since the ID is already a column in my table, could I concatenate them so to speak?
Actually, I haven't even been successful adding the URL into a new column, is that possible?

David Redden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-24 : 02:26:41

quote:
Originally posted by davidredden1973

Would this possibly work with a scenarios as such:
I need to create a url address.
So in one new column labeled URL1 I would put in the entire url address minus the field that will be the id= portion of the URL address.
Then, since the ID is already a column in my table, could I concatenate them so to speak?
Actually, I haven't even been successful adding the URL into a new column, is that possible?

David Redden

Post your question as a new topic

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources