Blog Post

Creating a “Real” Copy of a View: #SQLNewBlogger

,

I saw a post where a developer was trying to read the Information Schema views to create a copy of a view as a “real” table, a user table. This posts shows an easy way to do this.

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

The Scenario

Imagine you have a view, for example, I have this one:

CREATE   VIEW [dbo].[City] 
AS
SELECT TOP 10
  cn.CityNameID, cn.CityName
  FROM dbo.CityName AS cn
  WITH CHECK OPTION
GO

The structure of the underlying table is:

2024-11_0122

I have data being returned from this view as well, as you can see here:

2024-11_0118

If I want a copy of this view, I can certainly look in the information_schema views and see some data. Below, I have the column information for this view, which can be used to structure a create table statement.

2024-11_0119

However, there’s a better way.

Quickly Copying a View

The INTO clause is very valuable and helpful here. Many of us use this to copy a table or part of a table, but it work with views. Here is how I create an empty copy of my view.

SELECT * 
  INTO dbo.MyCities
  FROM dbo.city
  WHERE 1 = 0;

This will actually create a new table, as you can see in my Table list when I refresh after running the command.

2024-11_0120

The table looks like the structure of the view above. The PK isn’t set, but there isn’t necessarily a PK in a view as it can combine data from multiple tables. If I wanted data, I can run the same statement above without the WHERE clause. I’ve done that below and then selected data from the new table so show this.

2024-11_0123

If I needed to add some constraints or other items, I could easily add those with ALTER TABLE statements.

SQL New Blogger

This post required about 20 minutes for me to setup a demo, test, and then write with some screenshots. It wasn’t a hard post to write, but it shows a quick technique for doing something I’ve commonly seen from others.

This is the type of post you can write that might get an interviewer interested in you and perhaps ask you a question. You could add some context as to why you did this, or why you like (or don’t like) this technique.

Original post (opens in new tab)
View comments in original post (opens in new tab)

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating