XyzCoder.com


Collection of my learnings - Pavan Kumar Aryasomayajulu

Creating a New Table From A View IN SQL SERVER

Tags:

In this post, I want to show how we can create a table from a view. That is let's say I have a SQL View which consists of a join from various tables. So I want to create a table from this view.


Let me create few tables and a view.


Tables Creation:

Persons Table:

CREATE TABLE Persons (
    Id [int] IDENTITY(1,1) NOT NULL,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255) ,
	 CONSTRAINT [PK_Persons] PRIMARY KEY CLUSTERED ([Id] ASC)
);

INSERT INTO [dbo].[Persons]
([LastName],[FirstName],[Address],[City])
VALUES
('Pavan','Kumar','India','Vizag')

Schools Table:

CREATE TABLE School (
    Id [int] IDENTITY(1,1) NOT NULL,
    PersonId [int],
    SchoolName varchar(255),
    Address varchar(255),
    City varchar(255) ,
	 CONSTRAINT [PK_School] PRIMARY KEY CLUSTERED ([Id] ASC)
);

INSERT INTO [dbo].[School]
 ([PersonId],[SchoolName],[Address],[City])
View Creation:

CREATE VIEW Person_School_View AS
SELECT p.Lastname,p.firstname,p.address as person_address,p.city as person_city,
s.SchoolName,s.Address as school_address,s.city as school_city FROM Persons p
inner join School s
on p.id=s.personid


Now let's try to create a new table with columns selected in view

Syntax:
SELECT * INTO [New_Table] FROM [View]

Example:

SELECT * INTO Person_School_Table FROM Person_School_View

The above example will create a table with name 'Person_School_Table' and copies all the data from Person_School_View.

What if I don't want to copy data but just schema. If that is the case then we can have a dummy where condition which fails. For example

SELECT *
INTO Person_School_Table
FROM Person_School_View where 1=2

Note: This will only create a table.. If you want to copy data too, please remove where condition

So this will simply create a table with schema alone but will not copy any data.


Now let's say If I want to have an extra column that is not present in the view.Here I am adding an extra datetime column

SELECT *,CAST(NULL AS DATETIME) AS new_column
INTO Person_School_Table
FROM Person_School_View where 1=2

Note: This will only create a table.. If you want to copy data too, please remove where condition


If I want to add an identity column, I can use this and this will add an auto increment column Id

SELECT *,CAST(NULL AS DATETIME) AS new_column,IDENTITY (int,1,1) as Id
INTO PersonSchoolTable
FROM Person_School_View where 1=2

Note: This will only create a table.. If you want to copy data too, please remove where condition


We added an identity column and what if I want to have a primary key on that column. Unfortunately, we cannot have primary key column created in the Select Into statement. But we can alter the table.

  ALTER TABLE PersonSchoolTable 
ADD CONSTRAINT PK_PersonSchoolTable PRIMARY KEY (Id);


So instead of having two different statements, we can also have a stored proc created with these 2 statements combined.


CREATE  PROCEDURE AddTableFromView
AS
BEGIN

SELECT *,CAST(NULL AS DATETIME) AS new_column,IDENTITY (int,1,1) as Id
INTO PersonSchoolTable
FROM Person_School_View where 1=2

 ALTER TABLE PersonSchoolTable 
  ADD CONSTRAINT PK_PersonSchoolTable
  PRIMARY KEY (Id);

END




Thanks,

Pavan Kumar Aryasomayajulu


Comments Section:
Add Comment