none
To retrieval dbo.vTargetEmail view table query of AdventureWorksDW2012_Data at SQL Server 2012

    Question

  • I am learning SQL Server 2012 data warehouse now. I tried to SELECT Script View as/ SELECT TO/ New Query Edit Window from dbo.vTargetEmail view table at Microsoft SQL Server Management studio 2012 to try to view table result. Carelessly, I delete the existed query of dbo.vTargetEmail table of AdventureWorksDW2012_Data database.  So far dbo.vTargetEmail is disappeared.  How can I retrieval this view table back? I tried to download another database, that database didn't has the same view table.  Any one can send me a copy of view of dbo.vTargetEmail SELECT query syntax ?

    Monday, March 31, 2014 5:19 AM

All replies

  • Sure (though anyone could always download another copy of the data from Codeplex.com):

    USE [AdventureWorksDW2012]
    GO

    /****** Object:  View [dbo].[vTargetMail]    Script Date: 4/22/2014 6:58:23 PM ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO


    -- vTargetMail supports targeted mailing data model
    -- Uses vDMPrep to determine if a customer buys a bike and joins to DimCustomer
    CREATE VIEW [dbo].[vTargetMail]
    AS
        SELECT
            c.[CustomerKey],
            c.[GeographyKey],
            c.[CustomerAlternateKey],
            c.[Title],
            c.[FirstName],
            c.[MiddleName],
            c.[LastName],
            c.[NameStyle],
            c.[BirthDate],
            c.[MaritalStatus],
            c.[Suffix],
            c.[Gender],
            c.[EmailAddress],
            c.[YearlyIncome],
            c.[TotalChildren],
            c.[NumberChildrenAtHome],
            c.[EnglishEducation],
            c.[SpanishEducation],
            c.[FrenchEducation],
            c.[EnglishOccupation],
            c.[SpanishOccupation],
            c.[FrenchOccupation],
            c.[HouseOwnerFlag],
            c.[NumberCarsOwned],
            c.[AddressLine1],
            c.[AddressLine2],
            c.[Phone],
            c.[DateFirstPurchase],
            c.[CommuteDistance],
            x.[Region],
            x.[Age],
            CASE x.[Bikes]
                WHEN 0 THEN 0
                ELSE 1
            END AS [BikeBuyer]
        FROM
            [dbo].[DimCustomer] c INNER JOIN (
                SELECT
                    [CustomerKey]
                    ,[Region]
                    ,[Age]
                    ,Sum(
                        CASE [EnglishProductCategoryName]
                            WHEN 'Bikes' THEN 1
                            ELSE 0
                        END) AS [Bikes]
                FROM
                    [dbo].[vDMPrep]
                GROUP BY
                    [CustomerKey]
                    ,[Region]
                    ,[Age]
                ) AS [x]
            ON c.[CustomerKey] = x.[CustomerKey]
    ;

    GO


    Mark Tabladillo PhD (MVP, SAS Expert; MCT, MCITP, MCAD .NET) http://www.marktab.net

    Tuesday, April 22, 2014 10:58 PM