Hi, I have a table with 22 million records in a sql 7 server. I want  to copy
it to a SQL 2005 database.
What is the most quickest tool to use (BCP, Bulk insert, SSIS) and how.
Thanks in advance

Hi,

I will go with below steps:-

1. Take the script of the table and depandant objects from SQL 7
2. Create the table only in sql 2005
3. BCP OUT the data with batch option into a text file in SQL 7
4. Copy the text file to SQL 2005 server
5. Use either BULK INsert or BCP IN with Batch commit option to load the
data into SQL 2005 table
6. Create all indexes and Triggers (Dependant objects) from script we
created in step-1
7. Take a count of records and confirm the new table is good.

THanks
Hari
SQL Server MVP



Show quoteHide quote

Hi,
My understanding of your issue is:
You want to know which tool can best fit your requirement copying a huge
table with 22 million records of SQL 7 to SQL Server 2005 database, bcp,
BULK INSERT or SSIS.
If I have misunderstood, please let me know.

I recommend that you use SSIS to transfer the large data table from SQL 7.0
to SQL 9.0. Either bcp or BULK INSERT requires that the source data table
must be first be bulk copied into a file and then bulk copied into the
destination database table. However SSIS can turn away this spending.

Also, there is an article discussing the performance strategy of SSIS and
comparing the three tools, you can refer to:
SQL Server 2005 Integration Services: A Strategy for Performance
http://www.microsoft.com/technet/prodtechnol/sql/2005/technologies/ssisperfs
trat.mspx
From this article, you may notice that such loading operations may require
much memory, so I recommend that you set a larger buffer for
DefaultMaxBufferSize of your SSIS package.

If you have any other questions or concerns, please feel free to let me
know. It's my pleasure to be of assistance.

Charles Wang
Microsoft Online Community Support

======================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

Bug in Enterprise Manager (SQL 2000)
copy database error 12291 and 208
SQL Server 2005 Activity Monitor doesn't show connections from Windows 98 clients
differnt execution plans for same query
database mail...can't connect to mailserver