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:
I have data being returned from this view as well, as you can see here:
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.
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.
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.
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.
It’s the last T-SQL Tuesday of the year, and it’s amazing to think we’ve gotten to #181. That’s over 15 years of monthly blog parties.
Voice of the DBA 