5

I'm working with a SQL view that I created but I want to add in an ID column (identity seed) as the current one has none. How can I accomplish this in SQL View?

enter image description here

GabrielVa
  • 2,353
  • 9
  • 37
  • 59

4 Answers4

9

If there is no identity column in the underlying table, you can generate one with pseudo-columns.

In SQL server: SELECT ROW_NUMBER() OVER (ORDER BY FiscalYear, FiscalMonth), FiscalYear, FiscalMonth, ... FROM ... See http://msdn.microsoft.com/en-us/library/ms186734.aspx

In Oracle: SELECT ROWNUM, FiscalYear, FiscalMonth, ... FROM ... . In oracle, ROWNUM uses the order in the result set.

GeertPt
  • 16,398
  • 2
  • 37
  • 61
3

You can simply use the below to automatically add GUID to your VIEW:

CREATE VIEW VIEW_Name
AS
   NEWID() AS ID,
   your other columns here
FROM 
   dbo.YourTable
Gharibi
  • 89
  • 1
  • 8
2

If you don't actually care about actually being an identity seed. You could use ROW_NUMBER() to generate a superficial id.

Icarus
  • 63,293
  • 14
  • 100
  • 115
1

You must add the identity column to the underlying table, if it does not exist already. Then you can update the view to include this column.

A row number in no way functions as an identity (i.e., a PK). You have no guarantee this number will remain the same in the future. If in fact you just want a row number (that may change in the future), any of the solutions recommending that are fine, but if you want an identity that you can refer to elsewhere that is immutable, you must add this to the underlying table.

D'Arcy Rittich
  • 167,292
  • 40
  • 290
  • 283
  • That's not an answer to the question, and not helpful. – Maxx Aug 09 '19 at 22:10
  • It is an answer to the question, as it specifies "identity seed" which can only be done on the table itself, or via a sequence if using Oracle or similar. – D'Arcy Rittich Aug 13 '19 at 01:53