Replace VIEW with a TABLE...Impact to existing processes??

We have numerous processes that use a specific view:   MyDatabase.dbo.My_View

I'm interested in changing this view from a VIEW to a TABLE by use of running a stored procedure each night that will take the view code and create a literal / actual table in the DB to be used in place of the VIEW in all of our processes.

My question is this:

If I change a view by CREATING a new TABLE with the exact SAME NAME, will I need to do any code changes in any of my processes in order to have the processes use the VIEW code...?

(Note my plan is to DELETE the original VIEW and ONLY have the new table be active in the DB which will have the exact same name as the original view...)

I look forward to any feedback.

Thanks
LVL 17
MIKESoftware Solutions ConsultantAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
If the view is already there then you cannot create the tableName with the same name. Object Names should be unique in the database.

Please see below-

CREATE VIEW pawan
AS
SELECT * from five

CREATE TABLE pawan
(
 id int
)
GO

Open in new window


ERROR

Msg 2714, Level 16, State 6, Line 1
There is already an object named 'pawan' in the database.
0
Scott PletcherSenior DBACommented:
You should not have any query problems doing that.  Views and tables can be interchanged in a query without an issue.

The only thing you might have an issue with is that I've seen some people insist on using the object type when testing for its existence.  For example, if a check for the view is coded as:

IF OBJECT_ID('dbo.view_name_now_table_table', 'V') IS [NOT] NULL

In that case, naturally the code might not work as expected, since it won't find the object even though it's there.
0
Pawan KumarDatabase ExpertCommented:
Why are you doing this? is that for performance, you can also create index on the view to make is physical and fast. Also note that with that we will have few limitations also.

You will have to drop that view first and then create the table and fill that with your SP daily.

THE Script you need for this..

If EXISTS( SELECT 1 FROM SYS.VIEWS WHERE NAME = 'dbo.MyView' and TYPE = 'v')
BEGIN
DROP View [dbo].[MyView]
END

CREATE TABLE dbo.MyView ( Col DATATYPE...)

CREATE PROC procName
AS
BEGIN
         /*Other code*/

      ....INSERT DATA INTO dbo.MyView...
END

** - Note - YOU DO NOT HAVE CHANGE ANYTHING FROM WHERE you were calling your view. VIEW AND Table are same. Even VIEW/TABLE/SYNONYMS ARE SAME.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Scott PletcherSenior DBACommented:
Be sure to first record/generate a script of any permissions on the view, so that you can put them back on the table after it's created.
1
larryhSr. Software EngineerCommented:
Another approach that I might suggest is to create your table but using a name different from your view name.  In your existing view query the table you create instead of the tables it had been querying.  This way all the existing queries are still querying the same view but the basis for the view has changed.  This is one of the major benefits of a view: you can change its implementation without having to change any routines that reference it.
0
Scott PletcherSenior DBACommented:
I object.  Neither of Pawan's comments added anything of real value to this specific q.  The code in the second of his comments doesn't match his comments and is clearly something the author already knew how to do from the author's own comments.

I suggest the points be split as follows:

ScottPletcher -- 500 points
larryh -- 500 points
0
larryhSr. Software EngineerCommented:
Some good helpful responses but no feedback from the author.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.