Save ResultSet in an MS SQL Server DB

[adrianjohnson]

What I've got is a SQL query (which is fired at an Access DB), and the results are collected in a ResultSet, and displayed in a table.

What I'd like to do is save the results in a table in a SQL Server DB. I've written the "CREATE TABLE" scripts, so all that's ok, but how do I put the data in?

Is the only (or best?) way to do it is to read the ResultSet, and add the data line by line, or is there another method?

Thanks,

Adrian Johnson

 

[sedj]

Just to confirm - you want to in effect extract data from an Access [DB] and insert into to a SQL Server DB ?

--------------------------------------------------
Free Java/J2EE Database Connection Pooling Software

http://www.primrose.org.uk

 

[adrianjohnson]

Yep, that's correct.

After I wrote the above post, I did start writing the code to read the data directly from the table it's displayed in (using .getValueAt(r,c) ), but wondered if there was a better way.

 

[sedj]

There must be some bulk loaders out there, considering both products are M$ ... have you looked about ? Using JDBC seems a bit heavyweight for this kind of operation .

--------------------------------------------------
Free Java/J2EE Database Connection Pooling Software

http://www.primrose.org.uk

 

[adrianjohnson]

It's part of a bigger project which I've developed in Java - this is one of the smaller pieces I need.

THe other thing to mention (sorry I didn't earlier) is that I'm copying data from an Access DB to start with, but it might not always be that. The customer this is for have a FoxPro and Informix DB too.

I'll probably carry on with what I'm doing - I just wondered if there was a quick answer.

Thanks for your replies.

 

[sedj]

Considering there could be multiple datasources, then what you are doing is probably the best way. You may want to look at executeBatch(), but its a little bit more work.

--------------------------------------------------
Free Java/J2EE Database Connection Pooling Software

http://www.primrose.org.uk

 

[adrianjohnson]

I'll stick with what I'm doing, I think. The customer wants this bit of the project finished this week.

Thanks for your help, sedj.