By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,688 Members | 1,897 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,688 IT Pros & Developers. It's quick & easy.

Data transfer

P: n/a
I have a table in a database that has just over 1 million records, each
record about 50-60 characters. I need to transfer this data to another
database. I implemented two options:

1) Read the data record by record and insert into the destination
database, using ODBC. I played with different commit intervals, up to
100,000. This version takes about 12 minutes. Having indexes on the
destination table or removing them made little difference.

2) Dump the data into a flat file, and use bulk copy to read the file
into the destination table. This takes less than a minute.

Are these results within the normal ranges? I am particularly
interested in a way to improve upon the first method.

Jul 23 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
INSERT SELECT will probably improve on the first method.
When you say having indexes on the destination table made little
difference does that include a primary key and a clustered index? a
heap with no indexes at all should be used for best performance.

Jul 23 '05 #2

P: n/a
Tzvika Barenholz wrote:
INSERT SELECT will probably improve on the first method.
When you say having indexes on the destination table made little
difference does that include a primary key and a clustered index? a
heap with no indexes at all should be used for best performance.


I thought that since the original data is in a different SQL Server
instance, I couldn't use the INSERT SELECT statement. Is that correct?
As for the indexes, yes, it is a simple, single-column primary key.

Jul 23 '05 #3

P: n/a
Hi

If the server is a linked server then you can use INSERT... SELECT with
either a 4 part name or using OPENQUERY.

John

<ne**********@yahoo.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
Tzvika Barenholz wrote:
INSERT SELECT will probably improve on the first method.
When you say having indexes on the destination table made little
difference does that include a primary key and a clustered index? a
heap with no indexes at all should be used for best performance.


I thought that since the original data is in a different SQL Server
instance, I couldn't use the INSERT SELECT statement. Is that correct?
As for the indexes, yes, it is a simple, single-column primary key.

Jul 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.