469,603 Members | 2,057 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,603 developers. It's quick & easy.

Data transfer

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
3 1101
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
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
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.

Similar topics

2 posts views Thread by Fatih BOY | last post: by
7 posts views Thread by Mark Waser | last post: by
3 posts views Thread by David Veeneman | last post: by
5 posts views Thread by Donald Adams | last post: by
reply views Thread by devrayhaan | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.