create table from views

Question

Post reply

create table from views

Swimmer

SSC Enthusiast

Points: 100
More actions
August 20, 2008 at 1:52 pm

#192656

Now We use views on views and reports are running on the last views, so you can imagine reporting is very slow.
To find out if reporting is increasing I would like to create a table of one of those views.
So I used the following command:
View: V_Cars
SELECT * into Tbl_Cars FROM V_Cars.
But When I open the Tbl_Cars I only see the column headers of the view but all cells showing NULL
How is that possible?
if I run select * from V_Cars, I see all data
I also tried to run the following command:
CREATE TABLE Tbl_Cars
AS
SELECT * FROM V_Cars
But then a receive an error message saying, "incorrect syntax after AS"
Please advise me how to do this?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 1

You need to spend a bit more time learning how to create tables.

CREATE TABLE Tbl_Cars

AS

SELECT * FROM V_Cars

is not valid SQL. You cannot load data into a table that way.

Go to the HELP information and look up CREATE TABLE.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

nary SSCrazy Points: 2869 More actions · Answer 2

I won't find any problem with this syntax SELECT * into Tbl_Cars FROM V_Cars.

Why it's showing odd results for you.

Dugi SSCoach Points: 17998 More actions · Answer 3

VERY EASY ....

SELECT * INTO new_table_name

FROM any_kind_of_your_view

that's it!!!

:hehe::cool:

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 4

My apologies but but when I first replied I missed that you had tried:

SELECT * into Tbl_Cars FROM V_Cars

Try just the select statement

SELECT * FROM V_Cars

Does it return any records?

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

manoj2001 SSCrazy Points: 2225 More actions · Answer 5

SELECT * INTO New_Table_Name From Old_Table(View) will definately work properly over here.

Regards

Manoj