Win a copy of Java Mock Exams (software) this week in the Programmer Certification (OCPJP) forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

Data from SQL Server to MS Access

 
Roel De Nijs
Sheriff
Posts: 10763
148
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

I have to copy the content of several tables in a sql server database to tables (sometimes with the same structure, sometimes structure is different) in a ms access database. So i am wondering how you have to do such thing, because until now i just made a query, executed it on the appropriate database and handled the resultset

so any ideas are welcome and appreciated.

Many thanks!
 
Ulf Dittmer
Rancher
Posts: 42970
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The approach is similar here: query, execute, handle. The "handle" part would consist of inserting the data into a different DB. If there are many rows, you might use batch inserts instead of inserting them row by row, or at the least not committing them one by one, but only every 1000th row or so.

One thing to consider is primary key IDs. From your post it's not clear whether you might be able to use the same keys in both tables, or whether you need to create new ones (in which case foreign keys would no longer work).
 
Roel De Nijs
Sheriff
Posts: 10763
148
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
thanks for your quick reply

and how does a batch insert look like? could you provide a (code) example?

primary keys isn't in this situation a problem because i can use the ones i got from sql server
 
Ulf Dittmer
Rancher
Posts: 42970
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Take a look at the relevant section of the JDBC spec overview.
 
Roel De Nijs
Sheriff
Posts: 10763
148
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for the link, it will be very useful in future.

But i was thinking about something: isn't it possible to use a DTS to do all the copying and data transferring? I'm afraid it will be only be visual basic compliant, but you'll never know.

or maybe it's possible with a (or several) stored procedures.
[ January 27, 2006: Message edited by: Roel De Nijs ]
 
Happiness is not a goal ... it's a by-product of a life well lived - Eleanor Roosevelt. Tiny ad:
the new thread boost feature: great for the advertiser and smooth for the coderanch user
https://coderanch.com/t/674455/Thread-Boost-feature
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!