create table based on view

[mflancour]

How would I create a table and place data into that table from an existing view?

 

[SQLDenis]

select * into TableName
from ViewName

this will create the table and the will put the data into the table in one shot

Change TableName to the table Name that you want to use and change ViewName to the name of the view

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[mflancour]

Looks like this will work great ...excep, I must have a larger than normal dataset as I am receiving the following error:


Server: Msg 1701, Level 16, State 2, Line 1
Creation of table 'stuff1' failed because the row size would be 8239, including internal overhead. This exceeds the maximum allowable table row size, 8060.

Any way around this?

 

[SQLDenis]

a row in SQL server can onlyb be 8060 bytes (not including text and other BLOBs)
your view is a combination of several tables all these column combined are to wide to fit on a 8K page
Eliminate some columns by using a column list instead of *



Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[mflancour]

Sounds about right...I'm thinking I'm not getting the right error though because I have exacly 24 columns in the query most of wich are number fields, but even if I apply a 255 average size to all columns that's still only around 6200. Could it be something else?

 

[mflancour]

well, I found the problem...but not the solution yet.. I have a field that is for some reason being set to varchar 8000 when it creates the table. Any ideas on how to fix, what is causing this?