Simple Straightforward INSERT problem

[themaj]

Having another brain fart on what I believe is a no brainer.

I'm trying to read the contents of a DBF and write it to an ACCDB table; this is a daily event and the DBF has between 500-1200 records each day.

I have successfully read the contents of a DBF table and filled my da.Fill(ds, "Data") with it along with a DataGridView.

Now I want to write the entire table with NO parameters to my ACCDB Table 'BlahBlah'; both structures are the same. I see a ton of samples & examples WITH parameters, but no cmd.command INSERT examples to insert/copy everything.

Basically I want to: INSERT INTO [BLAHBLAH] SELECT * FROM ds.tables("data") ....something like that

Everything related to the connection string, data source, etc is fine; I've been using it for other procedures with no problems.

What I can't seem to get is:
- the INSERT statement
- whether I loop through the datatable row by row, or can copy thr whole thing in one blast

Appreciate someone's guidance and patience :)

[ss125]

I suggest you to see this link...

See here

Hope this helps you...

Have a happy coding...:-D

[Reverend Jim]

I don't think you can do this with INSERT INTO [BLAHBLAH] SELECT * FROM ds.tables("data") because you are using two connections. You can only do that type of insert when the databases are on the same server. You are not working with a large number of records so I'd just brute force record by record using ADO. Because you are not getting user input there should be no risk of SQL injection so parameterized queries aren't necessary. What you end up with looks pretty much like

Dim con1 As New ADODB.Connection
Dim con2 As New ADODB.Connection
Dim rec  As New ADODB.RecordSet

con1.Open(source db connection string)
con2.Open(destination db connection string)

rec.Open(selection query, con1, CursorTypeEnum.adOpenStatic)

Do Until rec.EOF
    Dim query As String = "INSERT INTO..."
    con2.Execute(query)
    rec.MoveNext
Loop

rec.Close()
con1.Close()
con2.Close()

Just add the field values into query by rec("fieldname").Value

[themaj]

Hey Jim, you got me confused here.

I already have all the records in a dataset/datatable and have closed the connection to the DBF file. Now I just want to write the datatable to my Access DB. The datatable has about 65 fields (exact same as Access table) so I'm trying to avoid identifying all these fields in the query. I just want to INSERT everything, whether it be by brute force or a bulk copy.

I just don't know how to express that in the cmd.commandtext query.

Make sense?

[Reverend Jim]

Ah. So if I have the sequence of events correct you want to

1. open the connection to the source database
2. populate the datatable from the source table
3. close the connection
4. connect the datatable to the destination database
5. insert from the datatable to the destination table

It makes sense but I am not well versed in datatables (I tend to avoid middlemen objects whenever possible) so I can't really offer a suggestion. If I think of one before someone else posts a solution I'll post it here.

[themaj]

ss125, I may have to go that direction but want to avoid it for now. I didn't think this task was that hard :(

All I want to do is INSERT the entire contents of my current dataset table to my ACCDB table. I don't care if it's done row by row; they're aren't that many rows.

I just need that INSERT statement .......arrrg!

[ss125]

@themaj,

I need some information...

Does your table contains any unique id's?

If so, I am having a algorithm to do what you need...

[themaj]

@ss125,

The data in the datatable does NOT have any unique IDs relative to any database structure, that will come later as they are introduced to the Access table with a AutoNumber field.
But there is but there is a 'uniqueness' to each line of data. It is impossible that any 2 rows could/would be identical.

Does that answer your question?

[themaj]

Does it uncomplicate the issue if I say there is 5 rows in the datatable instead of 500+?

It's still the same problem; how to INSERT the entire row without having to refer to each field.

That shouldn't be that complicated :(

[ss125]

Based on the design you said,eventhough if it is of 5 - 10 records there are no options than that of manual insertion.

But this is bulk. So either you have to use a sqlbulkcopy.

There is also another way, if your system supports reporting in excel sheet.

The ball is in your court...

You have to decide...

[themaj]

OK.....I'll accept this reality and try joining the modern world. Can someone tell me the difference between using a data adapter vs a cmd.commandtext to do my SELECT, INSERT, DELETE operations?

Is there any particular reasons, advantages, logic to use of one over the other? I notice that in previous projects I've used both and I'm not really sure why other than I 'found similar code and I could easily adapt it'.

Really want to learn here and I appreciate your opinions. At my age I don't have time to go back to school :)

[ss125]

Even I was wondering about this... But when I googled i got an information like this...

Updating a data source is much easier using DataAdapters. It's easier to make changes since you just have to modify the DataSet and call Update.

This is probably no (or very little) difference in the performance between using DataAdapters and Commands. DataAdapters internally use Connection and Command objects and execute the Commands to perform the actions (such as Fill and Update) that you tell them to do, so it's pretty much the same as using only Command objects.

Hope this helps you..

Have a happy coding...:-D