Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Data from SQL Server to MS Access

 
Roel De Nijs
Sheriff
Posts: 10662
144
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: 42969
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: 10662
144
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: 42969
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: 10662
144
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 ]
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic