编写存储过程：从指定路径Excel提取数据加载至SQL表

Alright, let's walk through building this stored procedure step by step. I'll cover setting up the target SQL table, writing the procedure to pull data from Excel, and handling key edge cases like existing records and data type mismatches.

First, we need a table with the required unique constraint on ProductName and Month to enforce your business rule. Adjust data types to match your actual Excel data (e.g., use VARCHAR for Month if it's stored as text like "Jan-2024" instead of a date):

CREATE TABLE MonthlyProductSales (
    ProductName VARCHAR(100) NOT NULL,
    [Month] DATE NOT NULL, -- Swap to VARCHAR(10) if your month is text-based
    SalesValue DECIMAL(18,2) NOT NULL,
    -- Enforce unique product-month combination
    CONSTRAINT UQ_ProductMonth UNIQUE (ProductName, [Month])
);

This procedure will use OPENROWSET to read the Excel file, clean up the data, and use MERGE to safely insert or update records (so you don't hit unique constraint errors if the same product-month combo already exists):

CREATE PROCEDURE LoadExcelSalesData
    @ExcelFilePath NVARCHAR(500), -- Path to your Excel file
    @SheetName NVARCHAR(100) = 'Sheet1' -- Default sheet name, adjust as needed
AS
BEGIN
    SET NOCOUNT ON;

    -- Optional: Check if the Excel file exists first
    DECLARE @FileExists BIT;
    EXEC master.dbo.xp_fileexist @ExcelFilePath, @FileExists OUTPUT;
    IF @FileExists = 0
    BEGIN
        RAISERROR('Error: The specified Excel file does not exist or is inaccessible.', 16, 1);
        RETURN;
    END

    -- Merge data from Excel into the target table
    MERGE INTO MonthlyProductSales AS Target
    USING (
        -- Read and clean data from Excel
        SELECT
            TRIM(ProductName) AS ProductName, -- Remove extra spaces from product names
            -- Convert Excel's month value to a DATE (adjust logic to match your Excel format)
            CASE
                WHEN ISDATE([Month]) = 1 THEN CAST([Month] AS DATE)
                -- If month is stored as "Jan-2024", convert to a valid date
                ELSE CAST('01-' + [Month] AS DATE)
            END AS [Month],
            CAST(SalesValue AS DECIMAL(18,2)) AS SalesValue -- Ensure numeric value is consistent
        FROM OPENROWSET(
            'Microsoft.ACE.OLEDB.12.0',
            'Excel 12.0 Xml;HDR=YES;Database=' + @ExcelFilePath,
            'SELECT * FROM [' + @SheetName + '$]'
        )
    ) AS Source
    -- Match on the unique product-month combination
    ON Target.ProductName = Source.ProductName AND Target.[Month] = Source.[Month]
    -- Update sales value if the combo already exists
    WHEN MATCHED THEN
        UPDATE SET Target.SalesValue = Source.SalesValue
    -- Insert new record if the combo doesn't exist
    WHEN NOT MATCHED THEN
        INSERT (ProductName, [Month], SalesValue)
        VALUES (Source.ProductName, Source.[Month], Source.SalesValue);

    -- Confirm success with row count
    PRINT 'Data load completed. Rows affected: ' + CAST(@@ROWCOUNT AS VARCHAR(10));
END
GO

Critical Notes for Smooth Operation #

• OLEDB Provider: Ensure the Microsoft.ACE.OLEDB.12.0 provider is installed on your SQL Server machine. For 64-bit SQL Server, use the 64-bit version of the provider.
• File Permissions: The SQL Server service account must have read access to the Excel file path. For network files, use a UNC path (e.g., \\server\shared\sales.xlsx) instead of a local drive letter.
• Excel Headers: The HDR=YES in the connection string assumes your Excel sheet has column headers matching ProductName, Month, and SalesValue. If not, change to HDR=NO and reference columns as F1, F2, F3.
• Data Type Adjustments: Tweak the CASE statement for [Month] to match your Excel's format. For example, if months are stored as integers (like 202401 for January 2024), use CAST(CAST([Month] AS VARCHAR(6)) + '01' AS DATE).

Call the procedure with your file path and sheet name:

EXEC LoadExcelSalesData 
    @ExcelFilePath = N'C:\SalesData\February2024_Sales.xlsx',
    @SheetName = N'MonthlySales';

内容的提问来源于stack exchange，提问作者samaposha