0

Is it possible to generate a unique ID for the auxiliary table? I am retrieving data from several tables, but I do not know how to create a new ID for the results:

I would like to have an additional column with ID.

I tried to look for several methods, but nothing helped me. I will be very grateful.

Greetings,

with ct as (
      select *
      INTO temp_table
from dba.view_NEW_Users_AreaCodes ur
     join dba.view_NEW_Customers_SalesTowns ct on ct.CustSalesTerritoryTTID = ur.UserAreaCodeID
where ur.UserType = 'TT'
      and ct.CustSalesTerritoryTTID <> 0
union all
select *
from dba.view_NEW_Users_AreaCodes ur
     join dba.view_NEW_Customers_SalesTowns ct on ct.CustSalesTerritoryMTID = ur.UserAreaCodeID
where ur.UserType = 'MT'
      and ct.CustSalesTerritoryMTID <> 0
union all
select *
from dba.view_NEW_Users_AreaCodes ur
     join dba.view_NEW_Customers_SalesTowns ct on ct.CustSalesTerritoryHRCID = ur.UserAreaCodeID
where ur.UserType = 'HRC'
      and ct.CustSalesTerritoryHRCID <> 0
union all
select *
from dba.view_NEW_Users_AreaCodes ur
     join dba.view_NEW_Customers_SalesTowns ct on ct.CustSalesTerritoryDevID = ur.UserAreaCodeID
where ur.UserType = 'DEV'
      and ct.CustSalesTerritoryDevID <> 0
     )
select row_number() over (order by newid()) as DATA_ID,
       ct.*
from ct;
Michał Zawada
  • 31
  • 1
  • 3

1 Answers1

0

You could use row_number():

with t as (
      < your query here >
     )
select row_number() over (order by newid()) as seqnum,
       t.*
from t;

newid() is just an arbitrary value that randomizes the numbering. You can use a column there if you prefer a more canonical ordering.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786