how to insert a huge data from mdb to sql 2000 ?

how to insert a huge data from mdb to sql 2000 ?

Post by kresna rud » Tue, 02 Sep 2003 16:13:50


I want to migrate my access data to sql 2000 server, but
the prolems are :
1. my access data is very huge (hundreds thousands rows),
can I use bcp or bulk insert to sql 2000 from ms access ?

2. Can we performs inserting data without recorded in
transaction log ?

thanks.
 
 
 

how to insert a huge data from mdb to sql 2000 ?

Post by Allan Mitc » Tue, 02 Sep 2003 16:21:35

Hundreds of thousands of rows is not an insignificant amount but likewise is
is not really an amount that should caue you concern.

A number of methods to move the data

1. Export to text file from Access and import using either BULK INSERT or
bcp
2. DTS
3. Access upsizing wizard.
4. Linked server to Access

All things are recorded in the log but to varying amounts. Look up RECOVERY
MODELs in BOL.

--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.yqcomputer.com/

 
 
 

how to insert a huge data from mdb to sql 2000 ?

Post by kresna rud » Tue, 02 Sep 2003 16:53:23

Dear friends,

I already try to use dts to import data from access to sql
2000 , performance is slow, it takes more than 5 menits,
because this is part of reporting system, I am afraid user
will complain, I try to use linkserver to retrieve data
from ms access but peformance is not good too.

My friends tell me, using dts with active X scripting is
slow, is it true ?

By the way, thans for your sugestions, I will try to put
temporary table in differents db and use simple mode
recovery, I hope performance is better.

Thanks.




very huge table.
SIMPLE.
perform
table1')
message

rows),
access ?
 
 
 

how to insert a huge data from mdb to sql 2000 ?

Post by Uri Diman » Tue, 02 Sep 2003 17:20:33

KRESNA
For now days hundreds thousands rows i would not called very huge table.
You cannot , but you set recovery mode of the database to SIMPLE.
Also ,consider create linked server to the .mdb and perform
SELECT * FROM OPENQUERY( test_Access1, 'select * from table1')

For more details please refer to BOL.
 
 
 

how to insert a huge data from mdb to sql 2000 ?

Post by kresna rud » Tue, 02 Sep 2003 17:25:08

have exactly near 750.000 rows, I gathered from joining
5 tables in Ms Access before insert them to sql 2000
server, I think you are right, I must delete indexes in
destination and may created them back after inserted, is
it better ? , but sorry I cannot remove indexes in Ms
Access.

increase in
be no quicker for
out out of hours
went home.
message
to sql
menits,
user
data
is
put
called
in
database to
server, but
in
 
 
 

how to insert a huge data from mdb to sql 2000 ?

Post by kresna rud » Tue, 02 Sep 2003 17:31:00

Uri, why should I remove indexes from ms access ? , I
think it is not relevan, I agree with suggestions from
Allan and you to remove indexes in destinations (SQL 2000)
because t-sql insert will update index too.

Thanks.

went home.
message

sql
user
to

but
 
 
 

how to insert a huge data from mdb to sql 2000 ?

Post by Allan Mitc » Tue, 02 Sep 2003 17:55:10

K I see now.


Indexes on the Source will help you get to the rows you want (Providing they
are well placed).

Is it a complicated SELECT ?

Have you tried importing all the tables to SQL Server and then use a view
over them to do the inserts ?

How long does it take to Export using Access your Access data to Text File ?



--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



"kresna rudy kurniawan" < XXXX@XXXXX.COM > wrote in message
news:05e401c37062$8dd4df30$ XXXX@XXXXX.COM ...


 
 
 

how to insert a huge data from mdb to sql 2000 ?

Post by Uri Diman » Tue, 02 Sep 2003 17:58:47

Kresna
Just a guess.
Try remove all indexes on .mdb database.
Also perhaps you need to transfer data when all users went home.
 
 
 

how to insert a huge data from mdb to sql 2000 ?

Post by Uri Diman » Tue, 02 Sep 2003 18:56:19

ok
I did not think you have already database on SQL Server. I thought you are
plane phase yet.
So , I agree you need to remove all indexes and triggres on SQL Server
database.