How to create a view with different column names than source table

Question

I have a table that stores email data. The challenge that I have is that some of the column names use MSSQL Reserved Words such as To and FROM.

I've place brackets around the column names and my Query works fine in Query Designer, but the challenge is I'll be calling it from ASP.Net. Bracket syntax in ASP.Net indicate it should inject data in place of the brackets.

To avoid all of this I want to create a View. How do I change the column names in the View to avoid Reserved Words?

SELECT        PortalID, [To], [From], Subject, Body, CreatedOnDate, CreatedByUserID
FROM            dbo.CoreMessaging_Messages

create view v (c1, c2, ...) as select ...

jarlh
– jarlh

2017-07-21 11:32:54 +00:00
Commented Jul 21, 2017 at 11:32 — jarlh
– jarlh, Commented Jul 21, 2017 at 11:32
The same you do in a any Select: [To] AS newname

dnoeth
– dnoeth

2017-07-21 11:33:21 +00:00
Commented Jul 21, 2017 at 11:33 — dnoeth
– dnoeth, Commented Jul 21, 2017 at 11:33
Thanks @dnoeth. That worked perfectly!

Martin Muldoon
– Martin Muldoon

2017-07-21 11:36:27 +00:00
Commented Jul 21, 2017 at 11:36 — Martin Muldoon
– Martin Muldoon, Commented Jul 21, 2017 at 11:36

Gordon Linoff · Accepted Answer · 2017-07-21 11:38:11Z

1

In a view, you can do:

create view v_table as
    select PortalID, [To] as to_whatever, [From] as from_whatever,
           Subject, Body, CreatedOnDate, CreatedByUserID
    from dbo.CoreMessaging_Messages;

A simpler method might be just to use computed columns:

alter table dbo.CoreMessaging_Messages
        add to_whatever as ([To]);

alter table dbo.CoreMessaging_Messages
        add from_whatever as ([From]);

Then the alternate names are available to whoever uses to the table.

answered Jul 21, 2017 at 11:38

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to create a view with different column names than source table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related