問題描述
這有效:
SELECT * FROM OPENDATASOURCE(
'Microsoft.ACE.OLEDB.12.0',
'Data Source=d:\JobFiles\MyFile.xlsx;
Extended properties=Excel 8.0')...MySheet$
也是這樣:
INSERT INTO OPENDATASOURCE(
'Microsoft.ACE.OLEDB.12.0',
'Data Source=d:\JobFiles\MyFile.xlsx;
Extended properties=Excel 8.0')...MySheet$
SELECT * FROM blahblahblah
那為什么不呢?
DELETE FROM OPENDATASOURCE(
'Microsoft.ACE.OLEDB.12.0',
'Data Source=d:\JobFiles\MyFile.xlsx;
Extended properties=Excel 8.0')...MySheet$
我做了一些搜索,但沒有真正的運氣.歸根結底,我需要的只是在插入新數據之前刪除 excel 行,我只想使用 SQL 來實現這一點.
I've done some searching with no real luck. At the end of the day, all I need is the excel rows deleted before I can insert fresh data and I want to achieve this with SQL only.
推薦答案
您不能通過 OPENDATASOURCE 刪除整行.根據 http://support.microsoft.com/kb/257819:
You cannot delete entire rows via OPENDATASOURCE. According to http://support.microsoft.com/kb/257819:
刪除
與刪除 Excel 數據相比,您在刪除 Excel 數據方面受到更多限制關系數據源.在關系數據庫中,行"沒有意義或記錄"之外的存在;在 Excel 工作表中,這不是真的.您可以刪除字段(單元格)中的值.但是,您不能:
You are more restricted in deleting Excel data than data from a relational data source. In a relational database, "row" has no meaning or existence apart from "record"; in an Excel worksheet, this is not true. You can delete values in fields (cells). However, you cannot:
- 一次刪除整個記錄,否則您會收到以下錯誤消息:
此 ISAM 不支持刪除鏈接表中的數據.
您只能通過清空每個記錄的內容來刪除記錄個人領域.
You can only delete a record by blanking out the contents of each individual field.
- 刪除包含 Excel 公式的單元格中的值,否則您會收到以下錯誤消息:
在這種情況下不允許操作.
- 您無法刪除已刪除數據所在的空電子表格行,并且您的記錄集將繼續顯示為空與這些空行對應的記錄.
這篇關于SQL Server:從 OPENDATASOURCE 中刪除的文章就介紹到這了,希望我們推薦的答案對大家有所幫助,也希望大家多多支持html5模板網!