Create table based on a view

[dwight1]

I want to create a table based on a view. Can any one advise me where i am going wrong?

create table AcctsFeb as
SELECT * FROM dbo.AcctsMonthly2
WHERE (Jan IS NOT NULL)

Thank you

Dwight

 

[SQLDenis]

You create views based on tables not the other way around

create view AcctsFeb as
SELECT * FROM dbo.AcctsMonthly2
WHERE (Jan IS NOT NULL)


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[Welshbird]

I think you need to do it this way

SELECT      
field1,
field2
INTO AcctsFeb
FROM  dbo.AcctsMonthly2
where (jan is not null)

Note that you cannot do select * in a select into, but if you want to change any column names you can do that as follows:

SELECT      
field1 as NewField1,
field2 as NewField2
INTO AcctsFeb
FROM  dbo.AcctsMonthly2
where (jan is not null)

Hope this helps.

Fee.

"The question should be, is it worth trying to do, not can it be done"

 

[dwight1]

SQLDenis,

I appreciate your quick response. But my scenario is i have some 12 views for 12 months. I want the results of this view to be stored in a table so that when i make a final view i can use the tables and not views (as this views generate the results after querying the server every time they are used). I want to avoid this step of querying the server. If i have 12 tables that would increase the speed and avoid running through the main table data.

Please advise

Thank you

Dwight

 

[SQLDenis]

So you are trying to implement horizontal partitioning?
Use willif's code, make sure you have check constraint on the months and you will have to create the indexes since select into won't create them for you

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[dwight1]

Willif,

Thank you very much. It worked like a charm.

I appreciate your help.

Thank you ALL

Dwight