How to write query generate unique Id for concate both columns search

ahmed elbarbary

5y809

I work on sql server 2012 . I face issue I can't generate unique Id from merge or concave both columns

Search Parts and Company ID and generated Id must be on column Generated ID

to be easier and fast on search

so Please How to generate unique Id from both column Search Parts and Company ID
and if both column repeated value both get same Id
so please How to do that

AS Example

SearchParts CompanyId GeneratedId
A5ghf7598f7GGHYUTYA 3456 901
when concate both columns Search Parts and Company Id generate Id
and take same Id in case of repeated
but main idea generate number unique for both column search parts and company id

every search parts text and CompanyId must be unique and take unique Id on generated Id

create table #partswithcompany
(
SearchParts nvarchar(200),
CompanyId int,
GeneratedId INT
)
insert into #partswithcompany (SearchParts,CompanyId,GeneratedId)
values
('A5ghf7598fdmlcpghjk',1234,NULL),
('AKLJGSA7598fdmlcpghjk',5870,NULL),
('A5ghfJKKJGHHGghjk',9081818,NULL),
('KHJLFFS8fdmlcpghjk',123345,NULL),
('A5ghf7598f7GGHYUTYA',3456,NULL),
('A5ghfJKKJGHHGghjk',9081818,NULL),
('A5ghf7598f7GGHYUTYA',3456,NULL),
('A5ghf7598f7GGHYUTYA',3456,NULL)
Expected Result
SearchParts CompanyId GeneratedId
A5ghf7598fdmlcpghjk 1234 5
AKLJGSA7598fdmlcpghjk 5870 9
A5ghfJKKJGHHGghjk 9081818 8
KHJLFFS8fdmlcpghjk 123345 6
A5ghf7598f7GGHYUTYA 3456 7
A5ghfJKKJGHHGghjk 9081818 8
A5ghf7598f7GGHYUTYA 3456 7
A5ghf7598f7GGHYUTYA 3456 7

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rijwan Ansari5y ago
Better use GUID, this will be unique for every records, NEWID() function generates unique GUID.

NEWID() itself is a function. when called returns a GUID value

Example:
INSERT INTO TableName (Col1 , Col2, Col3)

VALUES (1 , 'Value 1', NEWID())
Rajanikant Hawaldar5y ago
1. SELECT
2. [SearchParts]
3. , [CompanyId]
4. , DENSE_RANK() OVER (ORDER BY searchparts, companyID) AS [GeneratedID]
5. FROM [#partswithcompany];