Generating UniqueValue using Varchar

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 2008 Forums

Transact-SQL (2008)

Generating UniqueValue using Varchar

Author

Topic

amitranjan
Starting Member

45 Posts

Posted - 2010-03-29 : 03:04:28

I have two tables ProspectImport and Prospects. ProspectImports is a physical table but holds data temporarily. What our application is doing , is that its importing a XL sheet into ProspectImports table. Further if a user wants to import it for use, with some import button on some form he can import those on records of ProspectImport table to Prospect. So what I a want to say from above is that ProspectImport is also a table that will hold values to them until user imports. It could be a day, a week or a month. But thats not an issue.

Here comes a issue. I have to import records of ProspectImport to Prospect as said earlier. In the XL sheet there is a column name ProspectNo whose datatype is character but is storing a integer value. So as exists in ProspectImport and Prospect tables. ProspectNo is not a mandatory so it could be null too. What I want to do is that before importing i want to make them unique.
See below table with sample data

ProspectImport Prospect
100 101
102 6112
125 325
400 215
100 156
102 1012
1021 612
1245 3275
215
100 2185
101 1056
null
null

In above prospectimport has data that is to be imported to prospect table. But before that I need a check. If u match the two tables you can easily find some matching records. for example take first record 100 from prospectimport now search it in same table if it exists add a suffix -1 to matched one. Here 100 matches two so our new table will become. If another match found increment the suffix , -2, -3 and so on both the tables so that we have unique records. But the change shud be reflected only to ProspectImport table so that the prospectno in prospect table will be unique. No repeatation.

ProspectImport Prospect
100 100
102 6112
125 325
400 215
100-1 156
102 1012
1021 612
1245 3275
215
100-2 2185
101 1056
null
null

in case of null find the max value of column in prospectimport table and also match that value with existing prospectno in prospect table, if matches found add the same suffix to number and replace null with that number.

Hope it will be clear.

amit Ranjan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-29 : 05:26:17

do you mean you need to export only distinct values of column to prospect? or is your reqmnt to make repeated values unique before export by adding the suffix part?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

amitranjan
Starting Member

45 Posts

Posted - 2010-03-29 : 05:29:35

quote:
Originally posted by visakh16

do you mean you need to export only distinct values of column to prospect? or is your reqmnt to make repeated values unique before export by adding the suffix part?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

reqmnt to make repeated values unique before export by adding the suffix part

amit Ranjan

amitranjan
Starting Member

45 Posts

Posted - 2010-03-29 : 05:32:01

quote:
Originally posted by amitranjan

[quote]Originally posted by visakh16

do you mean you need to export only distinct values of column to prospect? or is your reqmnt to make repeated values unique before export by adding the suffix part?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

reqmnt to make repeated values unique before export by adding the suffix part

amit Ranjan

Please help, i need it asap with 1-2 hrs.. It would be a gr8 help. thanks in advance

amit Ranjan

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-29 : 06:00:26

Here is an approach but I think I have not complete checked what you want.


declare @temp table (ProspectImport varchar(255), Prospect varchar(255))
insert @temp
select '100', '101' union all
select '102', '6112' union all
select '125', '325' union all
select '400', '215' union all
select '100', '156' union all
select '102', '1012' union all
select '1021', '612' union all
select '1245', '3275' union all
select '215',''  union all
select '100', '2185' union all
select '101', '1056' union all
select null,''  union all
select null,''

select * from @temp

select 
case when rownum > 1 then ProspectImport + '-' + convert(varchar(10),rownum-1)
                     else ProspectImport end 
as ProspectImport,
Prospect
from
(select *, row_number() over (partition by ProspectImport order by Prospect) as rownum
from @temp)dt
where ProspectImport is not null

No, you're never too old to Yak'n'Roll if you're too young to die.

amitranjan
Starting Member

45 Posts

Posted - 2010-03-29 : 06:35:29

quote:

Originally posted by webfred

Here is an approach but I think I have not complete checked what you want.


declare @temp table (ProspectImport varchar(255), Prospect varchar(255))
insert @temp
select '100', '101' union all
select '102', '6112' union all
select '125', '325' union all
select '400', '215' union all
select '100', '156' union all
select '102', '1012' union all
select '1021', '612' union all
select '1245', '3275' union all
select '215',''  union all
select '100', '2185' union all
select '101', '1056' union all
select null,''  union all
select null,''

select * from @temp

select 
case when rownum > 1 then ProspectImport + '-' + convert(varchar(10),rownum-1)
                     else ProspectImport end 
as ProspectImport,
Prospect
from
(select *, row_number() over (partition by ProspectImport order by Prospect) as rownum
from @temp)dt
where ProspectImport is not null

No, you're never too old to Yak'n'Roll if you're too young to die.

It worked but, i think its selecting, i need to update the existing prospectimport table data with newly generated prospectnos.

amit Ranjan

amitranjan
Starting Member

45 Posts

Posted - 2010-03-29 : 06:42:40

quote:
Originally posted by amitranjan

quote:
Originally posted by webfred

Here is an approach but I think I have not complete checked what you want.

declare @temp table (ProspectImport varchar(255), Prospect varchar(255))
insert @temp
select '100', '101' union all
select '102', '6112' union all
select '125', '325' union all
select '400', '215' union all
select '100', '156' union all
select '102', '1012' union all
select '1021', '612' union all
select '1245', '3275' union all
select '215','' union all
select '100', '2185' union all
select '101', '1056' union all
select null,'' union all
select null,''

select * from @temp

select
case when rownum > 1 then ProspectImport + '-' + convert(varchar(10),rownum-1)
else ProspectImport end
as ProspectImport,
Prospect
from
(select *, row_number() over (partition by ProspectImport order by Prospect) as rownum
from @temp)dt
where ProspectImport is not null

No, you're never too old to Yak'n'Roll if you're too young to die.

It worked but, i think its selecting, i need to update the existing prospectimport table data with newly generated prospectnos.

amit Ranjan

can you provide me an example that will use only the two tables not the temp table. Or temp table is mandatory. Thanks in advance

amit Ranjan

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-29 : 07:18:20

quote:
Originally posted by amitranjan

quote:
Originally posted by amitranjan

quote:
Originally posted by webfred

Here is an approach but I think I have not complete checked what you want.

declare @temp table (ProspectImport varchar(255), Prospect varchar(255))
insert @temp
select '100', '101' union all
select '102', '6112' union all
select '125', '325' union all
select '400', '215' union all
select '100', '156' union all
select '102', '1012' union all
select '1021', '612' union all
select '1245', '3275' union all
select '215','' union all
select '100', '2185' union all
select '101', '1056' union all
select null,'' union all
select null,''

select * from @temp

select
case when rownum > 1 then ProspectImport + '-' + convert(varchar(10),rownum-1)
else ProspectImport end
as ProspectImport,
Prospect
from
(select *, row_number() over (partition by ProspectImport order by Prospect) as rownum
from @temp)dt
where ProspectImport is not null

No, you're never too old to Yak'n'Roll if you're too young to die.

It worked but, i think its selecting, i need to update the existing prospectimport table data with newly generated prospectnos.

amit Ranjan

can you provide me an example that will use only the two tables not the temp table. Or temp table is mandatory. Thanks in advance

amit Ranjan

It is just for clear example
Use only SELECT statement and replace @temp with actual table name

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources