asked
Very large INSERT into a table from a view based on multiple tables.
I have view that created on multiple tables and it contains the 800 columns and it returns the 40 million records. So now i want to load the view data to a physical table. i have tried the select * into table_name*** from view_name***.But it fails after ran the 16 hrs and giving the error message is space issue, doesn't allocate the space to the temp db. before start this query i have 250 GB space in drive and the total space allocated to temp Db. i have tried the Insert into table_name**** (col,col1,...) select (col,col1,...) from view_name***.but it is also fails after 15hrs. i have tried the Import and Export wizard and it is working fine but it takes 50 hrs to load the data. BCP not work here, Because view is created on multiple table. So please suggest me,is there any other way or need do any change on view or table. give simple way to import the data. Thanks, Siva
800 columns? SQL Server is a *relational* database. I'd be curious to find out what relational design requires 800 columns in a single table. If you're determined to use a relational database for this purpose, could you give us some more details about the data, how it's used in the business, what you're hoping to do with the data and so on? We may then be better placed to help you come to a solution. I'd be utterly amazed if the best possible *relational* solution is a single table of 40 million row by 800 columns. If you must have a table arranged this way, maybe a *relational* database tool is not the best way to achieve it.
What @GPO is saying is, why on earth are you creating an 800 column table with umpty-million records. It's a bad approach to working with SQL Server which is a relational storage engine. You're going to have all sorts of other problems with this monster table, not just getting it loaded.
Views nesting on views AND a single view of all data. These are common code smells within SQL Server:
https://en.wikipedia.org/wiki/Code_smell. You're making fundamental errors and you need to reassess your overall approach. I hate being the bearer of bad news, but I'm sure I'm correct on this.
So. This runs slow. It doesn't work how you want. You claim to know that I'm right that this is a fundamentally flawed approach. But, you want to keep it in place, but be told about some trick that will make it run faster? I'm sorry. There isn't. Because, it is a fundamentally flawed approach.
Yes, i have 800 columns in view and those are coming from multiple table and i have created the physical table and tried different way to import the data explain in above...So now i want the simple (fastest) way to load the data into a table. there is no indexes on view or table. that is just a normal view it's created on more than 50 tables and return the 800 columns.
Since the resource limit you're hitting is space for tempdb, it suggest that your query to select the data is dumping lots of information to tempdb. This could be because you have aggregates or that you're sorting, or just that the query required hash tables in order to retrieve all the data. You have two options. First, tune the query to see if you can eliminate, or reduce, anything that is using tempdb. Second, break down the query into smaller pieces. Do the data load in chunks. Try doing 100,000 records at a time. If you need to, join to a [tally table][1] in order to have a mechanism of identifying how many records you've processed. Extremely large data moves like this is not thing you normally want to do within a single T-SQL statement. It's now how SQL Server was designed to work. But, most importantly, see my comment and @GPO comment above. It really sounds like you're making a very poor design decision here. [1]:
http://www.sqlservercentral.com/articles/T-SQL/62867/
I'm telling you, without ever seeing the system, what you're doing with single massive table is not how SQL Server works. It just isn't. It's not going to work well. It's not going to support your business well. And having a single massive location for all the data rather than do the necessary labor to build individual queries for appropriate reports, will not work well. I can back these statements up with reams of data, experience, and an understanding of how the SQL Server engine works. You may be better served by looking at Analysis Services cubes if you have to have a single view of the data. You may also be better served by looking to a star schema. But your single table approach is just going to give you a world of pain, as you're already experiencing.
"...you don't no about my system..." That could be because you won't tell us about it. "...we con't change that view or not break the view..." So who can then? Someone can. Is it a system supplied by a vendor and locked down? If so, and if what you want to do (which we still can only guess at) is really that important you might need to go back to the vendor and get their advice.
Thank you so much for your support. As per our system we will make the all data under one view and we con't change that view or not break the view. i have tried to load the data yearly wise, But that is also fail due to the same temp DB space issue.Here Import and Export wizard is working fine but it's take 50 hrs time to load the data. You can say this is a poor design,Because you don't no about my system.Here i am loading the data into table, Because this table will be used in another 10 critical views. So do we have any other option to reduce this time to load the data into physical table. Thanks, Siva.
17 People are following this question.
