如何在存储过程中执行含INSERT的SQL脚本？替代数据库恢复方案

Got it, let's fix up your stored procedure to run that INSERT-heavy SQL file instead of using RESTORE. First, let's address a couple of issues in your current code, then walk through the replacement logic.

First: Fix the Table Deletion Step #

Your current sp_MSFOREACHTABLE 'delete from ?' will likely fail if your tables have foreign key constraints. To get around this, we need to temporarily disable constraints before deleting, then re-enable them afterward:

-- Disable all constraints to avoid foreign key errors during deletion
Exec sp_MSFOREACHTABLE 'ALTER TABLE ? NOCHECK CONSTRAINT ALL';
-- Delete all data from every table
Exec sp_MSFOREACHTABLE 'DELETE FROM ?';
-- Re-enable constraints to maintain data integrity
Exec sp_MSFOREACHTABLE 'ALTER TABLE ? CHECK CONSTRAINT ALL';

Option 1: Use xp_cmdshell with sqlcmd (Best for Large Scripts) #

This method calls the sqlcmd command-line tool to execute your SQL file directly. It's great for large scripts since it doesn't load the entire file into memory at once.

Here's the updated stored procedure with this logic:

CREATE PROCEDURE DropAndRestore 
    @filepath nvarchar(200) 
AS 
BEGIN 
    SET NOCOUNT ON; 

    -- Step 1: Delete all existing data (with constraint handling)
    Exec sp_MSFOREACHTABLE 'ALTER TABLE ? NOCHECK CONSTRAINT ALL';
    Exec sp_MSFOREACHTABLE 'DELETE FROM ?';
    Exec sp_MSFOREACHTABLE 'ALTER TABLE ? CHECK CONSTRAINT ALL';

    -- Step 2: Execute the SQL file using sqlcmd via xp_cmdshell
    -- Enable advanced options and xp_cmdshell (if not already enabled)
    EXEC sp_configure 'show advanced options', 1;
    RECONFIGURE;
    EXEC sp_configure 'xp_cmdshell', 1;
    RECONFIGURE;

    -- Build the sqlcmd command (adjust authentication if needed)
    DECLARE @cmd NVARCHAR(4000);
    -- Use Windows authentication by default; add -U/-P for SQL auth if required
    SET @cmd = 'sqlcmd -S ' + @@SERVERNAME + ' -d landofbeds -i "' + @filepath + '"';

    -- Run the command
    EXEC xp_cmdshell @cmd;

    -- Optional: Disable xp_cmdshell again for security
    EXEC sp_configure 'xp_cmdshell', 0;
    RECONFIGURE;
    EXEC sp_configure 'show advanced options', 0;
    RECONFIGURE;
END
GO

Key Notes for This Option: #

• Permissions: The SQL Server service account needs read access to the file path specified in @filepath. If it's a network share, ensure the service account has proper permissions there.
• xp_cmdshell Security: If you're concerned about leaving xp_cmdshell enabled, the code above enables it temporarily, runs the command, then disables it again.
• Path Spaces: Wrapping @filepath in quotes ensures the command works even if the path has spaces.

Option 2: Use OPENROWSET to Read and Execute the File (Better for Small-Medium Scripts) #

If you prefer not to use xp_cmdshell, you can read the entire SQL file into a variable and execute it with sp_executesql. This works well for smaller scripts but might hit memory limits with extremely large files.

Here's the alternative stored procedure:

CREATE PROCEDURE DropAndRestore 
    @filepath nvarchar(200) 
AS 
BEGIN 
    SET NOCOUNT ON; 

    -- Step 1: Delete all existing data (with constraint handling)
    Exec sp_MSFOREACHTABLE 'ALTER TABLE ? NOCHECK CONSTRAINT ALL';
    Exec sp_MSFOREACHTABLE 'DELETE FROM ?';
    Exec sp_MSFOREACHTABLE 'ALTER TABLE ? CHECK CONSTRAINT ALL';

    -- Step 2: Read and execute the SQL file
    DECLARE @sql NVARCHAR(MAX);
    -- Read the entire file into the @sql variable
    SELECT @sql = BulkColumn
    FROM OPENROWSET(BULK @filepath, SINGLE_BLOB) AS FileContent;

    -- Execute the SQL script
    EXEC sp_executesql @sql;
END
GO

Key Notes for This Option: #

• File Size: NVARCHAR(MAX) can hold up to 2GB of data, so this works for most scripts, but extremely large files may cause issues.
• Permissions: The SQL Server service account still needs read access to the file path.
• SQL Syntax: Ensure your SQL file doesn't contain syntax that breaks when executed via sp_executesql (e.g., GO statements won't work here—you'll need to remove them or split the script into batches manually).

Final Checks #

• Test the stored procedure with a small test SQL file first to ensure it works as expected.
• Verify that the @filepath parameter includes the full path to your SQL file (e.g., C:\Scripts\my_inserts.sql or \\Server\Share\inserts.sql).
• If you're using SQL Server Authentication with sqlcmd, add -U YourUsername -P YourPassword to the @cmd variable (though storing passwords in procedures isn't recommended—use Windows Auth where possible).

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