• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Data from SQL Server to MS Access

 
Sheriff
Posts: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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!
 
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Take a look at the relevant section of the JDBC spec overview.
 
Roel De Nijs
Sheriff
Posts: 11604
178
Hibernate jQuery Eclipse IDE Spring MySQL Database AngularJS Tomcat Server Chrome Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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 ]
 
CAUTION! Do not touch the blades on your neck propeller while they are active. Tiny ad:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic