?
Solved

Copy large amount of records via stored procedure?

Posted on 2007-11-26
7
Medium Priority
?
196 Views
Last Modified: 2010-04-21
Hello,

I have a view which returns close to 5 million rows of data.  I need to copy this data into a table of the same structure.

Is it possible to create a stored procedure which copies the data over in chunks (so it doesn't timeout)?

If so can you give an example or a link to a tutorial?

Thanks,
-Torrwin
0
Comment
Question by:Torrwin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 29

Accepted Solution

by:
QPR earned 1500 total points
ID: 20352902
Is this a one off?
You might want to consider creating a SSIS package.
Or use BCP
0
 
LVL 13

Author Comment

by:Torrwin
ID: 20352917
I'm not sure what a one off is, but I need to be able to execute it from a VB.NET app, so I don't think those are options.
0
 
LVL 29

Expert Comment

by:QPR
ID: 20352999
A "one off" is an event you only plan to do once.

http://www.vbrad.com/article.aspx?id=10
Explains the benefits of bcp over using stored procedures (speed) and has some vb class/exe that maybe you could include in your project?
0
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

 
LVL 13

Author Comment

by:Torrwin
ID: 20353213
No, this will happen several times a month.

Also, BCP appears to need the source to be a file, not a view.
0
 
LVL 12

Expert Comment

by:kselvia
ID: 20353951

select * into newTable from MyView

will transfer all rows into newTable, but 5 million rows is relatively small as far as I'm concerned, and that statement would not "time out" in any case. It will run until it completes, which should be 3-15 seconds depending on the complexity of the view.

The answer is yes you could make a stored procedure return chunks or ranges of data, but the problem probably lies in the view and retrieving ranges from that is probably not going to improve performance, and making a stored procedure return portions of data from it is probably not the best approach.

To get the first 100 rows

select top 1000 * from mytable

to get the 2nd 1000 rows

select top 1000 * from
( select top 2000 * from mytable order by ID desc )
order by ID ASC

where ID is the unique key in the result set

Make 5 versions of that statement, each retrieving 1 million rows.  I can pretty much guarantee that will perform far worse than what you have now though.

Can we see your view? Maybe we can suggest ways to improve the performance.

And yes you can use a view with bcp using the queryout option.

0
 
LVL 29

Expert Comment

by:QPR
ID: 20353969
0
 
LVL 13

Author Closing Comment

by:Torrwin
ID: 31411073
Basically, the BCP suggestion led me to discover the SQLBulkCopy commands which are BCP-based.
0

Featured Post

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question