Cập nhật hàng loạt SQL từ Excel

Qua. Jeremy Kadlec. cập nhật. 2008-07-15. Bình luận [11]. Có liên quan. 1. 2. 3. 4. 5. Thêm > Tích hợp Microsoft Excel

Tôi đã xem các mẹo trước đây của bạn [Xuất dữ liệu từ SQL Server sang Excel và Các tùy chọn khác nhau để nhập dữ liệu vào SQL Server] liên quan đến làm việc với dữ liệu Excel và SQL Server. Lệnh chính được sử dụng trong một trong các mẹo là OPENROWSET. Điều này có lợi cho chúng tôi vì trong môi trường của chúng tôi vì người dùng doanh nghiệp của chúng tôi cung cấp dữ liệu cho chúng tôi dưới dạng bảng tính Excel. Nhiều tình huống kết thúc bằng việc dịch sang mã INSERT, UPDATE hoặc DELETE trong một hoặc nhiều cơ sở dữ liệu SQL Server của chúng tôi. Chúng tôi luôn tải dữ liệu lên một bảng và sau đó bắt đầu quá trình. Mặc dù quy trình này hoạt động, bạn có quen thuộc với bất kỳ tùy chọn nào khác để trực tiếp thực hiện các thao tác CHÈN, CẬP NHẬT hoặc XÓA không?

Đúng - Bạn đã đúng, lệnh OPENROWSET có thể hỗ trợ trực tiếp các thao tác CHÈN, CẬP NHẬT hoặc XÓA như được minh họa trong các mẹo này. Xuất dữ liệu từ SQL Server sang Excel và các tùy chọn khác nhau để nhập dữ liệu vào SQL Server. Ngoài ra, lệnh OPENROWSET cũng có thể hỗ trợ các câu lệnh CHỌN trong đó một bảng được nối với bảng tính Excel. Hãy xem qua một số ví dụ với cơ sở dữ liệu mẫu của SQL Server 2005 AdventureWorks cho mỗi thao tác với một bảng tính Excel mẫu

điều kiện tiên quyết

Đối với tất cả các ví dụ này, vui lòng làm như sau

• Tải bảng tính Excel [Office 2003] này về để thực hiện các thao tác
• Để làm theo các ví dụ, hãy nhớ lưu bảng tính Excel vào thư mục có tên C. \MSSQLTips\
• Xem lại từng trang tính trong tệp Excel để xem các dữ liệu khác nhau
• Đảm bảo đóng bảng tính Excel trước khi chạy bất kỳ mã nào
• Xem lại từng lệnh bên dưới trước khi thực hiện chúng trong môi trường của bạn

OPENROWSET Ví dụ

Dưới đây là bốn ví dụ để cho thấy một số tính linh hoạt với lệnh OPENROWSET

CHỌN với mệnh đề THAM GIA và ĐẶT HÀNG THEO

Giải thích mã - Với truy vấn bên dưới, 5 bản ghi sẽ được trả về để hiển thị một câu lệnh INNER JOIN đơn giản có thể trả về một tập hợp kết quả duy nhất từ ​​cả hai dữ liệu trong bảng [[Sales]. [Nhân viên bán hàng]] và bảng tính Excel

SELECT SP.[SalesPersonID]
,SP.[TerritoryID]
,SP.[SalesQuota]
,SP.[Bonus]
,SP.[CommissionPct]
,SP.[SalesYTD]
,SP.[SalesLastYear]
,SP.[rowguid]
,SP.[ModifiedDate]
,T.[SalesPersonID]
,T.[TerritoryID]
FROM [AdventureWorks].[Sales].[SalesPerson] SP
INNER JOIN OPENROWSET['Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;Database=C:\MSSQLTips\1540_OPENROWSET_Examples.xls;', 
'SELECT SalesPersonID, TerritoryID FROM [SELECT_Example$]'] T
ON SP.[SalesPersonID] = T.[SalesPersonID]
AND SP.[TerritoryID] = T.[TerritoryID]
ORDER BY SP.[SalesPersonID], SP.[TerritoryID]
GO�

INSERT với câu lệnh SELECT

Giải thích mã - Với khối mã đầu tiên, năm bản ghi được chèn vào [AdventureWorks]. [Việc bán hàng]. [SalesPerson] bằng cách đọc dữ liệu từ trang tính INSERT_Example của bảng tính Excel. � Trong truy vấn thứ hai, dữ liệu được chèn vào được xác minh

INSERT INTO [AdventureWorks].[Sales].[SalesPerson][SalesPersonID, TerritoryID, SalesQuota, Bonus, CommissionPct, SalesYTD, SalesLastYear, rowguid, ModifiedDate]
SELECT SalesPersonID
,TerritoryID 
,SalesQuota 
,Bonus 
,CommissionPct 
,SalesYTD 
,SalesLastYear
,NEWID[]
,GETDATE[]
FROM OPENROWSET['Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;Database=C:\MSSQLTips\1540_OPENROWSET_Examples.xls;', 
'SELECT SalesPersonID, TerritoryID, SalesQuota, Bonus, 
CommissionPct, SalesYTD, SalesLastYear 
FROM [INSERT_Example$]'] 
GO SELECT * FROM [AdventureWorks].[Sales].[SalesPerson]
WHERE SalesPersonID IN [1, 2, 3, 4, 5]
GO

CẬP NHẬT với Tuyên bố THAM GIA

Giải thích mã - Với khối mã đầu tiên, năm bản ghi được cập nhật trong [AdventureWorks]. [Việc bán hàng]. [SalesPerson] bằng cách đọc dữ liệu từ trang tính UPDATE_Example của bảng tính Excel. � Trong truy vấn thứ hai, dữ liệu cập nhật được xác minh

UPDATE SP
SET SP.Bonus = T.Bonus
FROM [AdventureWorks].[Sales].[SalesPerson] SP
INNER JOIN OPENROWSET['Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;Database=C:\MSSQLTips\1540_OPENROWSET_Examples.xls;', 
'SELECT SalesPersonID, TerritoryID, SalesQuota, Bonus
FROM [UPDATE_Example$]'] T
ON SP.SalesPersonID = T.SalesPersonID
AND SP.TerritoryID = T.TerritoryID
AND SP.SalesQuota = T.SalesQuota
GO SELECT * FROM [AdventureWorks].[Sales].[SalesPerson]
WHERE SalesPersonID IN [1, 2, 3, 4, 5]
GO

XÓA với Tuyên bố THAM GIA

Giải thích mã - Với khối mã đầu tiên, năm bản ghi sẽ bị xóa trong [AdventureWorks]. [Việc bán hàng]. [SalesPerson] bằng cách đọc dữ liệu từ trang tính DELETE_Example của bảng tính Excel. � Trong truy vấn thứ hai, dữ liệu đã xóa được xác minh

DELETE SP
FROM [AdventureWorks].[Sales].[SalesPerson] SP
INNER JOIN OPENROWSET['Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;Database=C:\MSSQLTips\1540_OPENROWSET_Examples.xls;', 
'SELECT SalesPersonID, TerritoryID, SalesQuota, Bonus
FROM [UPDATE_Example$]'] T
ON SP.SalesPersonID = T.SalesPersonID
AND SP.TerritoryID = T.TerritoryID
GO SELECT * FROM [AdventureWorks].[Sales].[SalesPerson]
WHERE SalesPersonID IN [1, 2, 3, 4, 5]
GO

Phân tích chung

Về việc chuyển đổi mã của bạn sang phương pháp mới, tôi chưa gặp phải bất kỳ vấn đề nào về hiệu suất với 1000 bản ghi, nhưng đó dường như là mối quan tâm đầu tiên. Nếu bạn kiểm tra phương pháp và hiệu suất tổng thể không phải là vấn đề, thì hãy xem xét phương pháp. Cũng có thể nhanh hơn để thực hiện một CẬP NHẬT duy nhất như trường hợp của ví dụ trên so với tải lên [CHÈN. CHỌN] dữ liệu và sau đó thực hiện CẬP NHẬT dựa trên bảng mới. Ngoài ra, bằng cách sử dụng các lệnh được liệt kê ở trên so với quy trình hai bước, mã tổng thể có thể sạch hơn một chút. Cân nhắc thứ hai với SQL Server 2005 là cài đặt Cấu hình Diện tích Bề mặt phải được bật để sử dụng mã này trong cả hai trường hợp. Cuối cùng, với một trong hai cách tiếp cận, hãy đảm bảo dọn sạch mọi bảng tính Excel hoặc bảng tạm thời sau khi bạn hoàn thành chúng

• Nếu bạn gặp phải nhu cầu tải dữ liệu dựa trên Excel lên SQL Server, hãy xem xét các mẫu mã trong mẹo này như một tùy chọn để thực hiện việc đó
• Để thay thế cho các lệnh này, bạn cũng có thể xem các mẹo này
  • Xuất dữ liệu từ SQL Server sang Excel
  • Các tùy chọn khác nhau để nhập dữ liệu vào SQL Server
  • Nhập dữ liệu Excel bằng Dịch vụ tích hợp máy chủ SQL [SSIS] và xử lý các vấn đề về dữ liệu unicode và phi unicode
  • Mẹo MSSQL. com Thể loại. Dịch vụ tích hợp máy chủ SQL
  • Mẹo MSSQL. com Thể loại. Tích hợp Microsoft Office
• Một mục cần lưu ý với bất kỳ quy trình nào là đảm bảo tất cả các tệp, bảng, v.v. hỗ trợ được sắp xếp và/hoặc xóa khi chúng không còn cần thiết

Những bài viết liên quan

Xuất dữ liệu từ SQL Server sang Excel

Nhập dữ liệu unicode của Excel bằng Dịch vụ tích hợp máy chủ SQL

Nhập dữ liệu Excel vào SQL Server bằng cách sao chép và dán

Nhập dữ liệu Excel với Dịch vụ tích hợp máy chủ SQL

Xuất dữ liệu từ SQL Server sang Excel

Nhập dữ liệu Excel vào SQL Server bằng cách sao chép và dán

Báo cáo máy chủ SQL với Microsoft Excel

Sử dụng Máy chủ được Liên kết Máy chủ SQL để Truy vấn Tệp Excel

PowerPivot với Excel 2013

Xây dựng các Chỉ số Hiệu suất Chính [KPI] với PowerPivot

Truyền các giá trị truy vấn động từ Excel sang SQL Server

Trích xuất Danh sách SharePoint bằng Power Query

Gấp truy vấn trong Power Query để cải thiện hiệu suất

Truy xuất kích thước tệp từ hệ thống tệp bằng Power Query

Sử dụng Microsoft Query trong Excel để truy xuất dữ liệu máy chủ SQL

Cách tạo Biểu đồ Burndown trong Excel từ đầu

Đọc tệp Excel trong SQL Server bằng OPENROWSET hoặc OPENDATASOURCE

Tải dữ liệu Excel vào máy chủ SQL

Phân tích kết quả máy chủ SQL trong Excel với định dạng có điều kiện

Sử dụng tham số Excel để tự động xuất dữ liệu máy chủ SQL

Các bài viết phổ biến

Chuyển đổi ngày và giờ bằng SQL Server

Định dạng ngày máy chủ SQL với chức năng FORMAT

SQL Server ÁP DỤNG CHÉO và ÁP DỤNG NGOÀI

Ví dụ về con trỏ máy chủ SQL

SQL Server DROP TABLE IF EXISTS Các ví dụ

Toán tử SQL NOT IN

Cuộn nhiều hàng thành một hàng và cột cho dữ liệu SQL Server

Làm cách nào để biết bạn đang chạy phiên bản SQL Server nào

Giải quyết lỗi không thể mở kết nối với SQL Server

SQL Chuyển đổi ngày thành YYYYMMDD

Cộng và trừ ngày bằng cách sử dụng DATEADD trong SQL Server

Máy chủ SQL lặp qua các hàng của bảng mà không cần con trỏ

Sử dụng MERGE trong SQL Server để chèn, cập nhật và xóa cùng một lúc

Số lượng hàng của máy chủ SQL cho tất cả các bảng trong cơ sở dữ liệu

Cách lấy ngày hiện tại trong SQL Server

Nối các cột Máy chủ SQL thành một Chuỗi với CONCAT[]

Các cách so sánh và tìm sự khác biệt cho các bảng và dữ liệu SQL Server

Định dạng số trong SQL Server

Cơ sở dữ liệu máy chủ SQL bị kẹt trong trạng thái khôi phục

Hiển thị số dòng trong cửa sổ truy vấn SQL Server Management Studio