>in che senso logicamente errata??
Nel senso che ti tira fuori risultati errati 
>non capisco come farmi ritornare i dati che hanno stesso id e
>data di aggiornamento diversa..
>e poi come farmi ritornare i dati
>che non ci sono nella tabella sql server ma ci sono in quella
>access..
Supponiamo di avere nel file db1.mdb la tabella Students così strutturata:
Field Name Data Type
----------- ----------------------
StudentID Number (Long Integer)
FirstName Text (10)
LastName Text (10)
LastModify Date/Time
e con le seguenti righe:
StudentID FirstName LastName LastModify
----------- ---------- ---------- -----------
1 Lorenzo Benaglia 01/01/2008
2 Luca Bianchi 02/01/2008
3 Andrea Montanari 03/01/2008
Ora spostiamoci su SQL Server, definiamo una tabella analoga ed allineiamola con i dati presenti in Access:
USE tempdb;
CREATE TABLE dbo.Students(
StudentID int NOT NULL PRIMARY KEY,
FirstName varchar(10) NOT NULL,
LastName varchar(10) NOT NULL,
LastModify datetime NOT NULL
);
/* Inserirsco in SQL Server gli studenti presenti in Access */
WITH CTE_Access AS
(
SELECT *
FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0'
, 'Data Source=D:\Documenti\db1.mdb;User Id=admin;Password=;'
)...Students
)
INSERT dbo.Students
SELECT A.*
FROM CTE_Access AS A
LEFT JOIN dbo.Students AS S
ON A.StudentID = S.StudentID
WHERE S.StudentID IS NULL;
SELECT *
FROM dbo.Students;
/* Output:
StudentID FirstName LastName LastModify
----------- ---------- ---------- -----------------------
1 Lorenzo Benaglia 2008-01-01 00:00:00.000
2 Luca Bianchi 2008-01-02 00:00:00.000
3 Andrea Montanari 2008-01-03 00:00:00.000
(3 row(s) affected)
*/
/* Aggiorno lo StudentID 3 in Access */
UPDATE OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0'
, 'Data Source=D:\Documenti\db1.mdb;User Id=admin;Password=;'
)...Students
SET FirstName = 'Gianluca'
, LastName = 'Hotz'
, LastModify = CURRENT_TIMESTAMP
WHERE StudentID = 3;
/* Aggiorno in SQL Server gli studenti modificati in Access */
WITH CTE_Access AS
(
SELECT *
FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0'
, 'Data Source=D:\Documenti\db1.mdb;User Id=admin;Password=;'
)...Students
)
UPDATE dbo.Students
SET FirstName = A.FirstName
, LastName = A.LastName
, LastModify = A.LastModify
FROM CTE_Access AS A
JOIN dbo.Students AS S
ON A.StudentID = S.StudentID
WHERE S.LastModify <> A.LastModify;
SELECT *
FROM dbo.Students;
/* Output:
StudentID FirstName LastName LastModify
----------- ---------- ---------- -----------------------
1 Lorenzo Benaglia 2008-01-01 00:00:00.000
2 Luca Bianchi 2008-01-02 00:00:00.000
3 Gianluca Hotz 2008-07-24 11:11:48.000
(3 row(s) affected)
*/
DROP TABLE dbo.Students;
Ciao!
--
Lorenzo Benaglia
Microsoft MVP - SQL Server
http://blogs.dotnethell.it/lorenzo/
http://italy.mvps.org