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

how to import hundreds of CSV files into SQL server?

Hi,
I have about 500 CSV files, mostly they are unser same structure. I need to import them all into SQL server. I create a new table for each one based on the firstline as the column name. I can do them one by one but it is very time consuming and boring. Is there a fast way of doing this?

If someone give me a information, I promise to share all of MY CSV files. They are the zone chart from UPS and I have edit all of them.

Thanks.Well ... am working on the same myself ... will let you know as soon as i figure it out.|||Really? Someone here was complaining that it's nearly impossible to get your hands on updated info for this stuff!

Anyway, you'll have to be logged on with sysadmin fixed security role to do the following:

declare @.cmd varchar(8000)
create table #files (fname varchar(128) null)
insert #files exec master.dbo.xp_cmdshell [dir <your_directory> /b]
delete #files where fname is null

After this code is run you have a list of all the files that you want to import. If needed, you can incorporate a filter into your DIR command to filter out anything you don't want.

Now you're ready to build your loop to BULK INSERT each file.|||Now you're ready to build your loop to BULK INSERT each file.

Well .. bulk insert requires the table to be present and we both do not have that luxury ... have posted on this before and did not get a satisfactory answer ... so need to think of something else

http://www.dbforums.com/showthread.php?threadid=980175|||I'll send you a VB app to do this...|||I was working on a DTS package using a Dynamic Properties tast to pull file names from a table, and update the path of a Text Input file, but getting it to iterate is the kicker...|||The main problem for me is not to get it to iterate but to get the size of the columns and the column names.

Excel uploads are better coz you can make a linked server to them.|||With a Text Input file in a DTS package, you can set it up to pull the first row as column names. Sizes are, I believe, automatic, but probably defaults to NVARCHAR(255).|||So, how would you like to determine the field size?|||Well, I BULK INSERT into the same table, into either TEXT or IMAGE field. Actually, I have more than 1 table, and I am dealing with archiving EDI transactions coming from/going to the state. IMAGE datatype I use to store all their PDF's and DOC's with their daily ammendments, and TEXT field for the actual transaction files. I also implemented archiving on the tables with TEXT field, so that the database doesn't have to be a tera-byte size for 2 years worth of transactions to be available.|||Dynamically based on the data ...

In case of an excel .. i am using a opendatasource query and then issuing a select * into ... but am not able to do that on csv and flat files|||but if it's a CSV, then the length of a field can change from line to line. I see that working only if it is a fixed-length field file.|||Thats exactly the problem ... and DTS is not an option for me|||Working on it...|||Is it okay if the fields default to some larger size (VARCHAR(255)), then you can shrink them manually?|||How can you load a table that does not exist?

How are you going to know the layout to build it?

I still say that for flat files you either generate a format file and bcp it in, or load it all in to a table with 1 column varchar(8000) and parse the data in to the destination table...

csv is easy if the tables already built...

why aren't the tables built?

How are you going to derive the structure of the file?

on the fly?|||Try this.

Not too flashy, certainly not rock solid, but it should work.

Customize the connection string first, replacing <servername> and <databasename>.

Browse to the folder holding the text files (it will iterate through all files, so be sure you only have CSV files there).

It creates a table named after the text file, adds fields using the first row (all varchar(255)), and runs through the file inserting data.|||I only had two small csv files to test with. Let me know if there are any major bugs.

If you really want, I can go through the file contents first to find the MAX size of each field before creating the table, but I want to go home tonight...|||I was working on the UPS calculator for a while and forget anything else. I will test the zip file that bpdwork created and let you know what happened.

I have the calculator down. The database is kind of big, I don't know how to let everyone share it. send me a mail if you need to take a look.

How to Identify Hot Tables

Does anyone know how to identify the hottest, most active tables in a
database?

We have hundreds of users hitting a PeopleSoft database with hundreds
of tables. We are I/O bound on our SAN, and are thinking of putting
the hottest tables on a solid state (RAM) drive for improved
performance. Problem is: which are the hottest tables? Would like to
do this based on hard data instead of developer/vendor guesses.

Any suggestions are much appreciated.Hi

You could profile system usage, this will not give tables if you are using
stored procedures, but you could work it out knowing which procedures are
being used. Knowing the procedures being called also helps to put things
into context and allows you to examine query plans.

John

"Jeff Roughgarden" <jroughgarden@.stanfordalumni.org> wrote in message
news:b8143af9.0407071314.5bcc4ae3@.posting.google.c om...
> Does anyone know how to identify the hottest, most active tables in a
> database?
> We have hundreds of users hitting a PeopleSoft database with hundreds
> of tables. We are I/O bound on our SAN, and are thinking of putting
> the hottest tables on a solid state (RAM) drive for improved
> performance. Problem is: which are the hottest tables? Would like to
> do this based on hard data instead of developer/vendor guesses.
> Any suggestions are much appreciated.|||My immediate thought was "dbcc memusage" but I looked in the documentation
and it's now obsolete. Too bad. I think that it showed the top objects in
memory, which is almost exactly what you seek.

If I had to approach this myself, I might give some thought to evaluating
the locks taken out. They should give you at least some clue as to what
objects are important to SQL Server (but not all objects locked may be in
memory). I suppose I'd snapshot the lock list periodically and then develop
some processes to evaluate what was locked and summarize the objects locked
and sort of estimate how much storage of each is locked (you have table,
extend, page and row locks to consider). I don't think this will tell you
much about logs, though, which may be critical.

"Jeff Roughgarden" <jroughgarden@.stanfordalumni.org> wrote in message
news:b8143af9.0407071314.5bcc4ae3@.posting.google.c om...
> Does anyone know how to identify the hottest, most active tables in a
> database?
> We have hundreds of users hitting a PeopleSoft database with hundreds
> of tables. We are I/O bound on our SAN, and are thinking of putting
> the hottest tables on a solid state (RAM) drive for improved
> performance. Problem is: which are the hottest tables? Would like to
> do this based on hard data instead of developer/vendor guesses.
> Any suggestions are much appreciated.