Poti crea un "linked server" catre BD Access si apoi comanda INSERT tabela_access SELECT ... FROM tabela_sql_server:

EXEC sp_addlinkedserver @server = 'LINK_ACCESS_DB',
    @srvproduct = 'commandos1',
    @provider = 'Microsoft.Jet.OLEDB.4.0',
    @datasrc = 'C:\BazaDeDate.mdb' --bd Access

INSERT INTO LINK_ACCESS_DB...Produs (Cod,Denumire)
SELECT IDArticol, Denumire
FROM Articol --tabela din bd SQL Server
 

Doar pt. selectia datelor din BD Access poti utiliza fraza SQL:

SELECT *

FROM LINK_ACCESS_DB...Produs
 

Sau poti folosi OPENQUERY / OPENDATASOURCE :

EXEC sp_configure 'Ad Hoc Distributed Queries' , 1  --atentie la setarea 'Ad Hoc Distributed Queries'  care trebuie sa fie activa !
RECONFIGURE

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
      'C:\BazaDeDate.mdb';'admin';'',Produs)
SELECT IdArticol , Denumire
FROM Articol

EXEC sp_configure 'Ad Hoc Distributed Queries' , 0  --daca este cazul dezactivezi optiunea 'Ad Hoc Distributed Queries'
RECONFIGURE