OPENROWSET Import Excel to SQL Table with validation data

Transact-SQL
1

Hi All,

My SQl as below:
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;HDR=YES;IMEX=1;Database=C:\test.xlsx',
'SELECT * FROM [Sheet1$]
where [ID] not in (select ID from testDB.dbo.tblA)
')

I got error:
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.

If I remove the portion of validation as below, it's work fine:-
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;HDR=YES;IMEX=1;Database=C:\test.xlsx',
'SELECT * FROM [Sheet1$]
')

Please advise.

Thank you.

Regards,
Micheale

2

I think you need to have the WHERE clause OUTSIDE the OPENROWSET

SELECT * 
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 
                'Excel 12.0;HDR=YES;IMEX=1;Database=C:\test.xlsx', 
                'SELECT * FROM [Sheet1$]')
where [ID] not in (select ID from testDB.dbo.tblA)

personally I would use NOT EXISTS rather NOT IN. If there are DUPs for ID then EXISTS will be faster, and if ID could be NULL in testDB.dbo.tblA then the NOT IN will fail :frowning:

SELECT * 
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 
                'Excel 12.0;HDR=YES;IMEX=1;Database=C:\test.xlsx', 
                'SELECT * FROM [Sheet1$]') AS X
WHERE NOT EXISTS
      (
          SELECT *
          FROM testDB.dbo.tblA AS T
          WHERE T.ID = X.ID
      )

If that doesn't work and a Work Around is OK then I would use an intermediate #TEMP table:

SELECT * 
INTO #TEMP
FROM OPENROWSET('...)

and then

SELECT *
FROM #TEMP
WHERE NOT EXISTS ...
1 Like
3

Thank you.
You're brilliant.
Yes, after moved WHERE clause OUTSIDE the OPENROWSET.
It's work fine,

Regards,
Micheale