[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 158
  • Last Modified:

Transfer Large Amounts Of Records From View To Table

Hello,

I have a view with about 5 million records in it.

I need to transfer all of these records into a table, what is the fastest way to do this?  The way I initially tried to do it (and it takes forever) is:

INSERT INTO TABLE
SELECT * FROM VIEW

I'm running this query in my VB.NET program, not SQL Mgmt Studio.

Thanks,
-Torrwin
0
Torrwin
Asked:
Torrwin
1 Solution
 
YveauCommented:
maybe bcp is an option. from the command line run:

bcp "select * from database.schema.view" queryout x:\path\to\file.txt -c -E

and then to import it into the table:

bcp database.schema.table in x:\path\to\file.txt -c -E

run bcp -? for more details.
It is especially fast when no indexes and triggers are on the target table !

Hope this helps ...
0
 
TorrwinAuthor Commented:
I don't think I can do that programatically
0
 
YveauCommented:
... yeah, that's a good point ;-)
Sorry, missed that.

Are there indexes on the table ?
Try inserting the records in a heap table (no indexes) and after the insert, create the indexes if necessary.

Hope this helps ...

0
 
nmcdermaidCommented:
Try

INSERT INTO TABLE WITH (TABLOCKX)
SELECT * FROM VIEW WITH (TABLOCKX)


There's not else you can do besides this and removing indexes (as already suggested). The bottleneck is probably your disk speed.
0
 
Scott PletcherSenior DBACommented:
Hmm, I would have thought that would be the fastest way.

Probably the db is expanding and is getting too small an increment and/or it's set to expand by a % not a fixed amount.  Change the autogrow amount to be, say, 10M, *not* a %, and try again.

Also, if you can, add a NOLOCK hint to the main table:

INSERT INTO TABLE
SELECT * FROM VIEW WITH (NOLOCK)

NOLOCK does have potential issues, including temporarily inserted rows that get rolled back and temporarily deleted rows that get rolled back.  Still, for many purposes, you can use it without major issues.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now