INSERT from MSAccess to MsSQL

[ignas]

I've a problem when I use an INSERT query in Access to put some data on MSSQL through a linked table
(simple: "INSERT INTO remote_table ( column1,column2) SELECT col1,col2 FROM local_table"). I found that the records are added one-by-one so my trigger on remote_table executes many times with table one record in [inserted] table. My question is there any way to make Access move all record at once...

Ignas

[MAK]

The purpose of Insert trigger in sql server is to execute an action for every insert you make.

You can do a BULK insert in SQL Server in order to make transactions as a Non-Logged operation.

[ignas]

Thanks, but the problem is that MS Access produces "INSERT INTO ... SELECT FROM ..." operation for every record. For example when I run MS Access query looking like this:
INSERT INTO remote_table ( c1,c2)
SELECT <b>TOP 5</b> cl1,cl2 FROM local_table

on the SQL side the trigger is run 5 times (for every row), not one time for 5 rows.
And becouse I load a quite big portion of data (200 000 records) executing trigger so many times is a waste of time.
How to avoid that ? Thanks in advance.

PI

[MAK]

ASk your SQL server DBA to disable the trigger on that particular table for a while when you are connecting. (Do this during Off Hours)

[ignas]

We have some lack of understanding .
I want the trigger to be run, but not for each record, but for whole selection. When I do an INSERT INTO SELECT FROM query in Access, there are generated INSERT's for every record. So my trigger is executed many times and table inserted contains one record. I want the trigger to be fired once with inserted table filled with all records from INSERT query. Thanks for your patience .

[MAK]

It depends on what you have in the trigger. Say for example the trigger on that table just inserts a row in a logtable.

1. your dba can disable the trigger or you can run your ms-access process and execute insert statement like the trigger to a logtable. then enable the trigger.

or

2. your dba can write a procedure which will disable and enable trigger on that table.

before you run your ms-access bulk insert process execute the disable procedure and do the insert and execute the enable procedure.
then insert a row into your log table.