INSERT SQLString

[bjso]

Hi

Im trying to INSERT 152 rows in a table. To speed it up im trying to build a String with all the rows and then execute them all at once. Im using ADODB.Command but it looks like I can only insert one row at a time. Does anyone have an idea how to do this?

This is my code(Automation error):

szSQLString = ""
i = 0

Do While Not oRs2.EOF

szSQLString = szSQLString + "INSERT INTO...."

i = i + 1

If i = 152 Then

objCommand.CommandText = szSQLString

objCommand.Execute RecordsAffected:=lRecordsAffected, Options:=adCmdText Or adExecuteNoRecords

i = 0
szSQLString = ""

End If

oRs2.MoveNext
Loop


Best regards

/Bjso

[salvelinus]

Can't say I understand that code, but gave it code tags:

szSQLString = ""   'this is unnecessary
i = 0
 
Do While Not oRs2.EOF
   'this will make one string with 152 INSERT statements
   szSQLString = szSQLString + "INSERT INTO...."  'are you leaving out the rest of the SQL?
   i = i + 1 
 
   If i = 152 Then
      objCommand.CommandText = szSQLString
      objCommand.Execute RecordsAffected:=lRecordsAffected, Options:=adCmdText Or adExecuteNoRecords
      i = 0
      szSQLString = ""
   End If
 
   oRs2.MoveNext
Loop

SQL Insert syntax is:

INSERT INTO tblFoo (fld1, fld2,...fldn) VALUES (str1, str2,...strn)
'WHERE clause optional
WHERE fld1 = 'Booga Booga'

It looks like you're building a long string with 152 INSERT INTO statements, with no fields or values, although maybe you just left those out. If so, I don't see where you're giving them unique values for each record.
If unique values for each record are what you want, you'd need to execute 152 separate INSERT statements. I've never heard of multiple INSERT statements in one query, and it would have to be a big performance hit to execute a bunch of single statements in a row. If your data is in a file, you could just read that in, or since it seems your data comes from a recordset, you could make the query used in the recordset part of the INSERT statement instead.
If you don't want unique values (uncommon, but maybe n fields in each record have generic data), a plain INSERT statement would work.

[bjso]

Sorry, a bit confusing code.

I have a query which contains a large number of records (oRs2). which I want to insert to the db.

If I execute after every record it works fine, but it takes ages(se below)

Ex1:

Do While Not oRs2.EOF

objCommand.CommandText = "INSERT INTO Prognosis(PROJECT_ID,NAME,ACTIVITY_NO,DESCRIPTION,ACCOUNT_DATE,INTERNAL_QUANTITY,TYPE) " & _
"VALUES('" & oRs2.Fields(0).Value & "','" & oRs2.Fields(1).Value & "','" & oRs2.Fields(2).Value & "','" & oRs2.Fields(3).Value & "','" & oRs2.Field (4).Value & "','0','Upparbetad'); "

objCommand.Execute RecordsAffected:=lRecordsAffected, Options:=adCmdText Or adExecuteNoRecords

oRs3.MoveNext
Loop



To improve the performance I'm therefore trying to build a long string and execute when it gets to 152 (Someone told me building a string like this will speed up the insert process)Therfore I wrote this(se below), however its not working, it says there's something wrong at the end of the query. So I wonder if this is actually possible to do in VBA(excel) and then insert into ACCESS db. Or if I have to write the code in another way?

My code:

i = 0
Do While Not oRs2.EOF

szSQLString = szSQLString + "INSERT INTO Prognosis(PROJECT_ID,NAME,ACTIVITY_NO,DESCRIPTION,ACCOUNT_DATE,INTERNAL_QUANTITY,TYPE) " & _
"VALUES('" & oRs2.Fields(0).Value & "','" & oRs2.Fields(1).Value & "','" & oRs2.Fields(2).Value & "','" & oRs2.Fields(3).Value & "','" & oRs2.Field (4).Value & "','0','Upparbetad'); "

i = i + 1

If i = 152 Then

objCommand.CommandText = szSQLString
objCommand.Execute RecordsAffected:=lRecordsAffected,Options:=adCmdText Or adExecuteNoRecords
i = 0
szSQLString = ""

End If

oRs2.MoveNext
Loop

Best regards

/Bjso

[Ecniv]

uh ... if you are reading from a db table use the insert into sql statement on that table to the destination table.

Use the Access query builder for an example.
Open the query (or make one) that retrieves the data you want
Change the type to insert (drop down type list on a toolbar or via menu).
It will ask you for the destination table (which I guess you have already?) Pick it.
Then for each field, there is a dropdown choice of destination fields now on a new row displayed in the query builder.
Match the fields up.
If you want to see what this is in an Sql statement, then change the view to Sql and see how it can be done...