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

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.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.sqlpass.org
"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in message
news:041c01c37058$981b8840$a501280a@.phx.gbl...
> 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.|||KRESNA
For now days hundreds thousands rows i would not called very huge table.
> 2. Can we performs inserting data without recorded in
> transaction log ?
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.
"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in message
news:041c01c37058$981b8840$a501280a@.phx.gbl...
> 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.|||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.
>--Original Message--
>KRESNA
>For now days hundreds thousands rows i would not called
very huge table.
>> 2. Can we performs inserting data without recorded in
>> transaction log ?
>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.
>
>"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in
message
>news:041c01c37058$981b8840$a501280a@.phx.gbl...
>> 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.
>
>.
>|||Kresna
Just a guess.
Try remove all indexes on .mdb database.
Also perhaps you need to transfer data when all users went home.
"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in message
news:049001c3705e$1e8d4f80$a401280a@.phx.gbl...
> 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.
>
>
> >--Original Message--
> >KRESNA
> >For now days hundreds thousands rows i would not called
> very huge table.
> >> 2. Can we performs inserting data without recorded in
> >> transaction log ?
> >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.
> >
> >
> >"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in
> message
> >news:041c01c37058$981b8840$a501280a@.phx.gbl...
> >> 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.
> >
> >
> >.
> >|||I 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.
>--Original Message--
>How many rows do you have exactly ?
>What is the structure of the table?
>Removing the following from the destination should see an
increase in
>performance
>1. Indexes
>2. Triggers
>Also setting the recovery model to Simple will help.
>You will need to reapply afterwards.
>
>Exporting to text file then reimporting will I dare say
be no quicker for
>you. This as has been suggested would ideally be carried
out out of hours
>
>--
>--
>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
>
>"Uri Dimant" <urid@.iscar.co.il> wrote in message
>news:ujaZJ8FcDHA.1744@.TK2MSFTNGP12.phx.gbl...
>> Kresna
>> Just a guess.
>> Try remove all indexes on .mdb database.
>> Also perhaps you need to transfer data when all users
went home.
>> "kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in
message
>> news:049001c3705e$1e8d4f80$a401280a@.phx.gbl...
>> > 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.
>> >
>> >
>> >
>> >
>> > >--Original Message--
>> > >KRESNA
>> > >For now days hundreds thousands rows i would not
called
>> > very huge table.
>> > >> 2. Can we performs inserting data without recorded
in
>> > >> transaction log ?
>> > >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.
>> > >
>> > >
>> > >"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in
>> > message
>> > >news:041c01c37058$981b8840$a501280a@.phx.gbl...
>> > >> 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.
>> > >
>> > >
>> > >.
>> > >
>>
>
>.
>|||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.
>--Original Message--
>Kresna
>Just a guess.
>Try remove all indexes on .mdb database.
>Also perhaps you need to transfer data when all users
went home.
>"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in
message
>news:049001c3705e$1e8d4f80$a401280a@.phx.gbl...
>> 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.
>>
>>
>> >--Original Message--
>> >KRESNA
>> >For now days hundreds thousands rows i would not called
>> very huge table.
>> >> 2. Can we performs inserting data without recorded in
>> >> transaction log ?
>> >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.
>> >
>> >
>> >"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in
>> message
>> >news:041c01c37058$981b8840$a501280a@.phx.gbl...
>> >> 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.
>> >
>> >
>> >.
>> >
>
>.
>|||OK 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" <kresnark@.yahoo.com> wrote in message
news:05e401c37062$8dd4df30$a301280a@.phx.gbl...
> I 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.
> >--Original Message--
> >How many rows do you have exactly ?
> >What is the structure of the table?
> >
> >Removing the following from the destination should see an
> increase in
> >performance
> >
> >1. Indexes
> >2. Triggers
> >
> >Also setting the recovery model to Simple will help.
> >
> >You will need to reapply afterwards.
> >
> >
> >Exporting to text file then reimporting will I dare say
> be no quicker for
> >you. This as has been suggested would ideally be carried
> out out of hours
> >
> >
> >
> >--
> >
> >--
> >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
> >
> >
> >
> >"Uri Dimant" <urid@.iscar.co.il> wrote in message
> >news:ujaZJ8FcDHA.1744@.TK2MSFTNGP12.phx.gbl...
> >> Kresna
> >> Just a guess.
> >> Try remove all indexes on .mdb database.
> >> Also perhaps you need to transfer data when all users
> went home.
> >>
> >> "kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in
> message
> >> news:049001c3705e$1e8d4f80$a401280a@.phx.gbl...
> >> > 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.
> >> >
> >> >
> >> >
> >> >
> >> > >--Original Message--
> >> > >KRESNA
> >> > >For now days hundreds thousands rows i would not
> called
> >> > very huge table.
> >> > >> 2. Can we performs inserting data without recorded
> in
> >> > >> transaction log ?
> >> > >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.
> >> > >
> >> > >
> >> > >"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in
> >> > message
> >> > >news:041c01c37058$981b8840$a501280a@.phx.gbl...
> >> > >> 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.
> >> > >
> >> > >
> >> > >.
> >> > >
> >>
> >>
> >
> >
> >.
> >|||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.
"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in message
news:04da01c37063$5f92df40$a401280a@.phx.gbl...
> 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.
> >--Original Message--
> >Kresna
> >Just a guess.
> >Try remove all indexes on .mdb database.
> >Also perhaps you need to transfer data when all users
> went home.
> >
> >"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in
> message
> >news:049001c3705e$1e8d4f80$a401280a@.phx.gbl...
> >> 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.
> >>
> >>
> >>
> >>
> >> >--Original Message--
> >> >KRESNA
> >> >For now days hundreds thousands rows i would not called
> >> very huge table.
> >> >> 2. Can we performs inserting data without recorded in
> >> >> transaction log ?
> >> >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.
> >> >
> >> >
> >> >"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in
> >> message
> >> >news:041c01c37058$981b8840$a501280a@.phx.gbl...
> >> >> 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.
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >