Replace views with tables

Question

I have numerous tables I that I want to have created and populated dynamically based on views.

I want to perform something like a combination of these two posts:

Create Table from View

Is there a way to loop through a table variable in TSQL without using a cursor?

Select *
Into dbo.##tblTemp
From databasename.sys.views

Declare @TableName NVARCHAR(128)

While (Select COUNT(*) From #Temp) > 0

Begin

    Select Top 1 @TableName = name from databasename.sys.views

        Select * into @TableName from databasename.sys.views

    Delete databasename.sys.views Where name = @TableName

End

Am I better off with a stored procedure that dynamically creates the sql statement to create the table?

EDIT:

Per Sebastian, I am running the below code to accomplish this:

DECLARE @cmd NVARCHAR(MAX) = ( SELECT TOP 10 'exec sp_rename '
                                    + '@objname =''' + OBJECT_SCHEMA_NAME(object_id)
                                    + '.'
                                    + OBJECT_NAME(object_id) + ''
                                    + ''', @newname = '
                                    + '''v_' + name + ''
                                    + ''';'
                                    + 'SELECT * INTO '
                                    + OBJECT_SCHEMA_NAME(object_id)
                                    + '.'                                   
                                    + OBJECT_NAME(object_id)
                                    + ' FROM '
                                    + OBJECT_SCHEMA_NAME(object_id)
                                    + '.v_'                                 
                                    + OBJECT_NAME(object_id)
                                    + ';'
                                    + 'DROP VIEW '
                                    + OBJECT_SCHEMA_NAME(object_id)
                                    + '.v_' 
                                    + OBJECT_NAME(object_id)
                                    + ';'
                            FROM db.sys.views
                            FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)');

EXEC (@cmd)
--Select @cmd

Answer 1

To copy all the data seems to be the wrong approach to solve your problem. If your problem is poor performance do to too many reads caused by views there are two things to look out for first.

1) Check that you tables have appropriate indexes. You could even add indexes to your views. There are many resources out there that tell you how to go about index tuning. Or you could hire a consultant (like me) to help you out with that.

2) If your queries join views, it often happens that unnecessary tables make it into the mix. For example if view v1 joins table a and b and view v2 joins table b and c and your query then joins v1 with v2, it effectively joins a with b with b with c. Such a query often can be rewritten to join to b only once helping tremendously with performance. So if you have queries joining views with views you should review those.

If after all that you still want to go forward with copying the data, you can use this:

DECLARE @cmd NVARCHAR(MAX) = ( SELECT 'SELECT * INTO '
                                      + QUOTENAME(OBJECT_SCHEMA_NAME(object_id))
                                      + '.'
                                      + QUOTENAME('tbl_'+OBJECT_NAME(object_id))
                                      + ' FROM '
                                      + QUOTENAME(OBJECT_SCHEMA_NAME(object_id))
                                      + '.' + QUOTENAME(OBJECT_NAME(object_id))
                                      + ';'
                               FROM   sys.views
                               FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)');

EXEC (@cmd);     

It creates a command that uses SELECT INTO to create a table for each view in the database. Because SQL Server does not allow name collisions even for objects of differing type, I prefixed the names of the tables with "tbl_".

If you need to create the tables in a different database, you need to prefix the table names with "dbname.". In that case you can remove the "tbl_"prefix.

---

EDIT:

You had a few missing quotes in your version. Try this:

DECLARE @cmd NVARCHAR(MAX) = ( SELECT TOP 1 'exec sp_rename '''
                                + QUOTENAME(OBJECT_SCHEMA_NAME(object_id))
                                + '.'
                                + QUOTENAME(OBJECT_NAME(object_id))
                                + ''', '''
                                + QUOTENAME(OBJECT_SCHEMA_NAME(object_id))
                                + '.'
                                + QUOTENAME('v_' +OBJECT_NAME(object_id))
                                + ''';'
                                + 'SELECT * INTO '
                                + QUOTENAME(OBJECT_SCHEMA_NAME(object_id))
                                + '.'                                   
                                + QUOTENAME(OBJECT_NAME(object_id))
                                + ' FROM '
                                + QUOTENAME(OBJECT_SCHEMA_NAME(object_id))
                                + '.'                                   
                                + QUOTENAME('v_' +OBJECT_NAME(object_id))
                                + ';'
                                + 'DROP VIEW '
                                + QUOTENAME(OBJECT_SCHEMA_NAME(object_id))
                                + '.' 
                                + QUOTENAME('v_' +OBJECT_NAME(object_id))
                                + ';'
                        FROM sys.views
                        FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)');

You also can use PRINT @cmd instead of EXEC(@cmd) to see if the command put together makes sense.