CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
wootscootinboogie
wootscootinboogie

Reputation: 8695

Adding IDENTITY insert to a view

I'm working on rolling my own cube and I have a table that looks something like

drug_class      drug_name
h3b             drug x
h4b             drug y
h2f             drug z

these columns are not the primary key and can be repeated N times for every record in the table. I want to make a dimension out of them for my cube and was wondering if it's possible to do an identity insert into a view like

create view [My Dimension] as
select distinct drugID int identity,drug_class,drug_name
from myTable

I know I can get a distinct list and do an identity insert into a table, but I was wondering if there's a way to do it with a view

Upvotes: 0

Views: 987

Answers (1)

Taryn
Taryn

Reputation: 247710

The easiest way to do this is to use row_number() to create the unique id for each row:

create view [My Dimension] as
select 
  row_number() over(order by drug_class) drugid,
  drug_class,
  drug_name
from yourtable

See SQL Fiddle with Demo

Upvotes: 2

Related Questions