CODEWITHSUNDEEP
sql-serversql-server-2008viewprimary-key
Kavitha
Kavitha

Reputation: 1467

How to add a new column (as a primary key column ) to view

Is it possible to add the column (PK) to view. if yes..How can we?

This is my view :

CREATE VIEW [dbo].[SalesDetailView]
AS
SELECT
    DATENAME(yyyy, SH.CreatedDateTime) AS Year,
    DATENAME(mm, SH.CreatedDateTime) AS Month, SH.CreatedDateTime AS Date,
    SH.TransactionName AS Type, SH.SalesHeaderID AS No,
    Customer.CustomerName AS Customer,
    CustomerGroup.CustomerGroupName AS Customer_Group, SH.Reference AS Memo,
    Item.ItemName AS Item, SD.LineDescription AS Item_Description,
    Item.ItemType AS Item_Type, Item.UOM,
    ItemGroup.ItemGroupName AS Item_Group,
    CAST (SD.Quantity AS INT) AS Quantity, CAST(SD.Amount AS MONEY) AS Amount,
    SD.Price, SD.Discount, SH.ExchangeRate AS Exchange_Rate,
    Currency.CurrencyDescription AS Currency, SD.ClassID AS Class_ID,
    SD.SalesTaxID AS SalesTax_ID, SalesTaxGroup.SalesTaxGroupName AS Tax_Group,
    Employee.EmployeeName AS Salesperson,
    ShippingMethod.ShippingMethodName AS Shipping_Method,
    PaymentTerm.PaymentTermName AS Payment_Term,
    PaymentMethod.PaymentMethodName AS Payment_Method
FROM
    SalesHeader SH, Customer
LEFT OUTER JOIN 
    SalesDetail SD ON SH.SalesHeaderID = SD.SalesHeaderID
LEFT OUTER JOIN 
    Item ON SD.ItemID = Item.ItemID
LEFT OUTER JOIN 
    ItemGroup ON Item.ItemGroupId = ItemGroup.ItemGroupID
LEFT OUTER JOIN 
    CustomerGroup ON Customer.CustomerGroupId = CustomerGroup.CustomerGroupID
LEFT OUTER JOIN 
    Employee ON Customer.EmployeeID = Employee.EmployeeID
LEFT OUTER JOIN 
    Currency ON Customer.CurrencyID = Currency.CurrencyID
LEFT OUTER JOIN 
    SalesTaxGroup ON Customer.SalesTaxGroupID = SalesTaxGroup.SalesTaxGroupID
LEFT OUTER JOIN 
    PaymentTerm ON Customer.PaymentTermID = PaymentTerm.PaymentTermID
LEFT OUTER JOIN 
    ShippingMethod ON Customer.ShippingMethodID = ShippingMethod.ShippingMethodID
LEFT OUTER JOIN 
    PaymentMethod ON Customer.PaymentMethodID = PaymentMethod.PaymentMethodID
WHERE
    SH.CustomerID = Customer.CustomerID
    AND SH.TransactionName <> 'SalesOrder'
    AND Sh.TransactionName <> 'Quote'

Upvotes: 0

Views: 610

Answers (2)

Ruzbeh Irani
Ruzbeh Irani

Reputation: 2438

When you see the sp_help of the view, the identity column that is mentioned is nothing but the identity of the underlying table.

If you have already added the identity column in the underlying table, you just need to alter your view and add the identity column in the select stmt.

Else you need to add the identity to your table first and then edit the view to add the column in your select stmt.

ALTER  VIEW [dbo].[SalesDetailView]
AS
SELECT
    DATENAME(yyyy, SH.CreatedDateTime) AS Year,
    DATENAME(mm, SH.CreatedDateTime) AS Month, SH.CreatedDateTime AS Date,
    SH.TransactionName AS Type, SH.SalesHeaderID AS No,
    Customer.CustomerName AS Customer,
    CustomerGroup.CustomerGroupName AS Customer_Group, SH.Reference AS Memo,
    Item.ItemName AS Item, SD.LineDescription AS Item_Description,
    Item.ItemType AS Item_Type, Item.UOM,
    ItemGroup.ItemGroupName AS Item_Group,
    CAST (SD.Quantity AS INT) AS Quantity, CAST(SD.Amount AS MONEY) AS Amount,
    SD.Price, SD.Discount, SH.ExchangeRate AS Exchange_Rate,
    Currency.CurrencyDescription AS Currency, SD.ClassID AS Class_ID,
    SD.SalesTaxID AS SalesTax_ID, SalesTaxGroup.SalesTaxGroupName AS Tax_Group,
    Employee.EmployeeName AS Salesperson,
    ShippingMethod.ShippingMethodName AS Shipping_Method,
    PaymentTerm.PaymentTermName AS Payment_Term,
    PaymentMethod.PaymentMethodName AS Payment_Method,
    [your column] as  PK
FROM
    SalesHeader SH, Customer
LEFT OUTER JOIN 
    SalesDetail SD ON SH.SalesHeaderID = SD.SalesHeaderID
LEFT OUTER JOIN 
    Item ON SD.ItemID = Item.ItemID
LEFT OUTER JOIN 
    ItemGroup ON Item.ItemGroupId = ItemGroup.ItemGroupID
LEFT OUTER JOIN 
    CustomerGroup ON Customer.CustomerGroupId = CustomerGroup.CustomerGroupID
LEFT OUTER JOIN 
    Employee ON Customer.EmployeeID = Employee.EmployeeID
LEFT OUTER JOIN 
    Currency ON Customer.CurrencyID = Currency.CurrencyID
LEFT OUTER JOIN 
    SalesTaxGroup ON Customer.SalesTaxGroupID = SalesTaxGroup.SalesTaxGroupID
LEFT OUTER JOIN 
    PaymentTerm ON Customer.PaymentTermID = PaymentTerm.PaymentTermID
LEFT OUTER JOIN 
    ShippingMethod ON Customer.ShippingMethodID = ShippingMethod.ShippingMethodID
LEFT OUTER JOIN 
    PaymentMethod ON Customer.PaymentMethodID = PaymentMethod.PaymentMethodID
WHERE
    SH.CustomerID = Customer.CustomerID
    AND SH.TransactionName <> 'SalesOrder'
    AND Sh.TransactionName <> 'Quote'

Upvotes: 0

Andomar
Andomar

Reputation: 238076

You could add a unique column with row_number, like:

CREATE VIEW [dbo].[SalesDetailView]
AS
SELECT
    row_number() over (order by SH.CreatedDateTime) as PK,
    DATENAME(yyyy, SH.CreatedDateTime) AS Year,

If that's not what you mean, please clarify your question. Some example results are always helpful.

Upvotes: 1

Related Questions