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