Insert Data from an Access table to a similar table in MS SQL Server

[Josef P.]

Note:
In the example in #24, you can also use (global) temporary tables (e. g. ##GUID-string) as the target and then use a direct sql statement (or call a sql procedure) via pass-though query or ADODB/OLEDB connection to insert the data into the original table.

The problem is that each data record is transferred individually via ODBC. If a trigger then runs in the target table, for example, it runs for each individual data record.

With Insert into Tab (...) select ... an Insert SQL statement is executed for each data record.
Profiler log:

exec sp_executesql N'INSERT INTO  "dbo"."Tab123"  ("ID","P","Z") VALUES (@P1,@P2,@P3)',N'@P1 int,@P2 nvarchar(255),@P3 int',1,NULL,NULL
exec sp_executesql N'INSERT INTO  "dbo"."Tab123"  ("ID","P","Z") VALUES (@P1,@P2,@P3)',N'@P1 int,@P2 nvarchar(255),@P3 int',2,N'JP16',NULL
...

With select ... into ... the insert statement is converted into a temp procedure and this procedure is called for each data record.

declare @p1 int  set @p1=1  exec sp_prepexec @p1 output,N'@P1 int,@P2 nvarchar(4000),@P3 int',N'INSERT INTO "Tab123" VALUES (@P1,@P2,@P3)',1,NULL,NULL  select @p1
exec sp_execute 1,2,N'JP16',NULL
...

 

[CJ_London]

An append query takes 15 minutes, a comparable make table query only takes 18 seconds

Not sure about 15 minutes v 18 seconds but a ‘performance enhancer’ for appends is to remove all indexes from the destination table, append the new data then recreate the indexes.

care required re potential duplicates and other constraints of course

Can’t confirm it as not at my computer but pretty sure a make table does not create indexes but if it does it adds the data then creates the indexes

 

[Josef P.]

Note on David's idea:

You are using a loop to collect the data to build the string. You are reading 1000 records to build the string. That takes a loop.

It then makes a bulk insert on the SQL-Server side.
And since reading takes much less time than writing, this method can certainly be an advantage when transferring.
(Instead of a PT query, you could also implement the insert via ADODB. However, this should be similarly fast.)

In .net you could pass a DataTable to a procedure. Unfortunately, this is not so easily available for VBA.
Alternatively, you could consider transferring the data as JSON or XML to an SQL procedure.
I would also test whether an ADODB/OLEDB recordset with UpdateBatch is already running fast enough. (But this is also not a bulk insert on SQL-Server side).

However, I would only use these variants if the connection to the SQL-Server is so bad that the simple insert statement via the linked table cannot be implemented.
If it is my SQL-Server, I would try to find out where the bottleneck is. Is it the transfer of the data, the processing of the individual data records in the table, etc.

 

[nector]

Many thanks

cheekybuddha​

I will surely settle on your solution , also warm regards to all the contributors all your answers are valuable to me.

 

[nector]

Yes sir, from my own experience, using the SQL server can only be good if you are running Views for reports. But using bound forms to the table with the form's property turned to data entry still in terms of speed it is slow to about 25 seconds compared to a form bound to an MS Access table. Worse off if there are a couple of combo boxes. Even if people claim that you need to filter the record set attached to the form , but a form which has the property on with data entry and all combo boxes filled with lazy combo sand box which loads with absolutely no data at all , it does not open or save data instantly it takes about 26 seconds or after some the so called catching to somewhere around 10 seconds.

True reports bound to a view or pass through query in terms of speed it can match Ms Access. If Microsoft can increase the storage capacity for Ms Access to at least 200 GB then this server thing will have no market at all and I think that is the reason they cannot do that to ensure that the server does not die.

 

[nector]

Either you are doing something wrong like binding your forms to tables or queries with no criteria and using Access to filter locally.

With total respect to what you are saying all my data entry form have their data entry properties set to "YES", and so they open without a single record at all, all my combo boxes open empty until the users type in the Combo box the first 3 characters only then can a small or short list pop up for the matching words and then users select from there.

Example below is a code without a sand box to initialize the combo box

Option Compare Database
Option Explicit
Private INVECCustomerIDLookup As New weComboLookup

Private Sub Form_Load()
    INVECCustomerIDLookup.Initialize Me.CustomerID, 3
    INVECCustomerIDLookup.UnfilteredRowSource = "SELECT CustomerID,Company FROM tblCustomers WHERE Company Like '**'ORDER BY Company;"
    INVECCustomerIDLookup.IsLazyLoading = True
    Me.CustomerID.Requery
End Sub

Example of a binded form to my table, see photo, as you can see we do not edit any data just direct data entry not editing at all. These forms open empty no data at all.

 

[Minty]

I'm not sure if setting a form to data entry stops it loading the entire table in the background?
What if you set the recordsource to tblCustomerInvoices where 1 = 0 ?

 

[nector]

If you are suggesting some like below:

DoCmd.OpenForm "frmCustomerInvoice", Where [InvoiceID] = 0"

 

[cheekybuddha]

If you are suggesting some like below:

DoCmd.OpenForm "frmCustomerInvoice", Where [InvoiceID] = 0"

Probably more like:

DoCmd.OpenForm "frmCustomerInvoice", Where:="1 = 0"