SQL Server INSERT操作实战与脚本生成方法

SQL Server INSERT操作实战与脚本生成方法

一、基础INSERT语法

1. 基本INSERT语句

-- 基本语法
INSERT INTO 表名 (列1, 列2, 列3, ...)
VALUES (值1, 值2, 值3, ...);

-- 示例：插入单条记录
INSERT INTO Employees (FirstName, LastName, DepartmentID, HireDate, Salary)
VALUES ('张三', '李', 1, '2023-01-15', 5000.00);

-- 简化写法（当插入所有列且顺序一致时）
INSERT INTO Employees
VALUES ('张三', '李', 1, '2023-01-15', 5000.00);

2. 插入多条记录

-- SQL Server 2008及以上版本
INSERT INTO Employees (FirstName, LastName, DepartmentID, HireDate, Salary)
VALUES 
    ('王五', '赵', 2, '2023-02-10', 5500.00),
    ('赵六', '钱', 3, '2023-03-15', 6000.00),
    ('孙七', '孙', 1, '2023-04-20', 4800.00);

二、高级INSERT操作

1. INSERT...SELECT（从其他表复制数据）

-- 将符合条件的记录插入到新表
INSERT INTO NewEmployees (FirstName, LastName, DepartmentID, Salary)
SELECT FirstName, LastName, DepartmentID, Salary
FROM Employees
WHERE HireDate > '2022-01-01'
  AND Salary > 4500;

-- 带计算的INSERT
INSERT INTO EmployeeStats (DepartmentID, AvgSalary, EmployeeCount)
SELECT 
    DepartmentID,
    AVG(Salary) as AvgSalary,
    COUNT(*) as EmployeeCount
FROM Employees
GROUP BY DepartmentID;

2. INSERT...EXEC（执行存储过程插入）

-- 执行存储过程并将结果插入表中
INSERT INTO EmployeeLog (LogTime, Action, EmployeeID)
EXEC sp_GetEmployeeActivity @Date = '2023-06-01';

3. 带OUTPUT的INSERT（返回插入的数据）

-- 返回插入的ID和其他信息
DECLARE @InsertedData TABLE (
    EmployeeID INT,
    FullName NVARCHAR(100),
    InsertTime DATETIME
);

INSERT INTO Employees (FirstName, LastName, DepartmentID, HireDate)
OUTPUT 
    inserted.EmployeeID,
    inserted.FirstName + ' ' + inserted.LastName,
    GETDATE()
INTO @InsertedData
VALUES ('周', '八', 2, '2023-05-01');

-- 查看插入的数据
SELECT * FROM @InsertedData;

三、实战脚本生成方法

1. 使用SELECT生成INSERT脚本

-- 方法1：手动构建脚本
SELECT 
    'INSERT INTO Employees (FirstName, LastName, DepartmentID, HireDate, Salary) ' +
    'VALUES (''' + 
    FirstName + ''', ''' + 
    LastName + ''', ' + 
    CAST(DepartmentID AS VARCHAR) + ', ''' + 
    CONVERT(VARCHAR(10), HireDate, 120) + ''', ' + 
    CAST(Salary AS VARCHAR) + ');'
FROM Employees
WHERE EmployeeID BETWEEN 1 AND 10;

-- 方法2：使用CONCAT（SQL Server 2012+）
SELECT CONCAT(
    'INSERT INTO Employees (FirstName, LastName, DepartmentID, HireDate, Salary) ',
    'VALUES (',
    QUOTENAME(FirstName, ''''), ', ',
    QUOTENAME(LastName, ''''), ', ',
    DepartmentID, ', ',
    QUOTENAME(CONVERT(VARCHAR(10), HireDate, 120), ''''), ', ',
    Salary,
    ');'
)
FROM Employees;

2. 使用动态SQL生成批量INSERT

DECLARE @SQL NVARCHAR(MAX) = '';

-- 生成批量INSERT语句
SELECT @SQL = @SQL + 
    'INSERT INTO Employees VALUES (' +
    QUOTENAME(FirstName, '''') + ', ' +
    QUOTENAME(LastName, '''') + ', ' +
    CAST(DepartmentID AS NVARCHAR) + ', ' +
    QUOTENAME(CONVERT(NVARCHAR(10), HireDate, 120), '''') + ', ' +
    CAST(Salary AS NVARCHAR) + ');' + CHAR(13)
FROM Employees
WHERE DepartmentID = 1;

-- 执行生成的SQL
PRINT @SQL; -- 先查看生成的脚本
-- EXEC sp_executesql @SQL; -- 确认无误后执行

3. 使用存储过程生成INSERT脚本

CREATE PROCEDURE GenerateInsertScripts
    @TableName NVARCHAR(128),
    @WhereClause NVARCHAR(MAX) = NULL
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @ColumnList NVARCHAR(MAX) = '';
    DECLARE @ValueList NVARCHAR(MAX) = '';
    DECLARE @SQL NVARCHAR(MAX) = '';

    -- 获取列名列表
    SELECT @ColumnList = STRING_AGG(COLUMN_NAME, ', ')
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = @TableName
    ORDER BY ORDINAL_POSITION;

    -- 构建动态SQL
    SET @SQL = '
    SELECT 
        ''INSERT INTO ' + @TableName + ' (' + @ColumnList + ') '' +
        ''VALUES ('' +
        STUFF((
            SELECT '', '' + 
                CASE 
                    WHEN DATA_TYPE IN (''char'', ''varchar'', ''nchar'', ''nvarchar'', ''text'', ''ntext'', ''date'', ''datetime'', ''datetime2'', ''time'', ''uniqueidentifier'') 
                    THEN QUOTENAME(CONVERT(NVARCHAR(MAX), [' + COLUMN_NAME + ']), '''''''')
                    WHEN DATA_TYPE IN (''bit'')
                    THEN CAST([' + COLUMN_NAME + '] AS NVARCHAR(MAX))
                    ELSE CAST([' + COLUMN_NAME + '] AS NVARCHAR(MAX))
                END
            FROM INFORMATION_SCHEMA.COLUMNS c2
            WHERE c2.TABLE_NAME = ''' + @TableName + '''
                AND c2.COLUMN_NAME = c1.COLUMN_NAME
            FOR XML PATH('''')
        ), 1, 2, '''') + '');''
    FROM ' + @TableName + ' t
    CROSS JOIN INFORMATION_SCHEMA.COLUMNS c1
    WHERE c1.TABLE_NAME = ''' + @TableName + '''
    GROUP BY t.*';

    -- 添加WHERE条件
    IF @WhereClause IS NOT NULL
        SET @SQL = @SQL + ' AND ' + @WhereClause;

    -- 执行并返回结果
    EXEC sp_executesql @SQL;
END;
GO

-- 使用示例
EXEC GenerateInsertScripts 'Employees', 'DepartmentID = 1';

四、性能优化与最佳实践

1. 批量插入优化

-- 方法1：使用事务
BEGIN TRANSACTION;
INSERT INTO Employees (...) VALUES (...);
INSERT INTO Employees (...) VALUES (...);
INSERT INTO Employees (...) VALUES (...);
COMMIT TRANSACTION;

-- 方法2：批量插入（SQL Server 2008+）
INSERT INTO Employees (...)
SELECT ... FROM SourceTable;

-- 方法3：使用BULK INSERT（大容量插入）
BULK INSERT Employees
FROM 'C:\data\employees.csv'
WITH (
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
    FIRSTROW = 2
);

2. 错误处理与事务

BEGIN TRY
    BEGIN TRANSACTION;

    INSERT INTO Employees (...)
    VALUES (...);

    INSERT INTO EmployeeLog (...)
    VALUES (...);

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;

    -- 记录错误信息
    INSERT INTO ErrorLog (ErrorTime, ErrorMessage)
    VALUES (GETDATE(), ERROR_MESSAGE());

    THROW; -- SQL Server 2012+
END CATCH;

五、实用工具脚本

1. 生成表数据的INSERT脚本

CREATE OR ALTER PROCEDURE sp_GenerateTableInsertScript
    @TableName NVARCHAR(255),
    @TopCount INT = NULL,
    @WhereClause NVARCHAR(MAX) = NULL
AS
BEGIN
    DECLARE @ColumnList NVARCHAR(MAX);
    DECLARE @SelectSQL NVARCHAR(MAX);
    DECLARE @FinalSQL NVARCHAR(MAX);

    -- 获取列名
    SELECT @ColumnList = STRING_AGG(
        QUOTENAME(COLUMN_NAME), ', '
    )
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = @TableName
    ORDER BY ORDINAL_POSITION;

    -- 构建SELECT语句
    SET @SelectSQL = 'SELECT ' + 
        CASE WHEN @TopCount IS NOT NULL 
             THEN 'TOP ' + CAST(@TopCount AS NVARCHAR) + ' ' 
             ELSE '' 
        END +
        '*' +
        ' FROM ' + QUOTENAME(@TableName);

    IF @WhereClause IS NOT NULL
        SET @SelectSQL = @SelectSQL + ' WHERE ' + @WhereClause;

    -- 使用FOR XML PATH构建VALUES部分
    SET @FinalSQL = '
    SELECT 
        ''INSERT INTO ' + QUOTENAME(@TableName) + ' (' + @ColumnList + ') '' +
        ''VALUES ('' +
        STUFF((
            SELECT '', '' + 
                CASE 
                    WHEN DATA_TYPE IN (''varchar'', ''nvarchar'', ''char'', ''nchar'', ''text'', ''ntext'', ''date'', ''datetime'', ''datetime2'', ''smalldatetime'', ''time'')
                    THEN COALESCE(''N'''''' + REPLACE(CONVERT(NVARCHAR(MAX), [' + COLUMN_NAME + ']), '''''''', '''''''''''') + '''''''', ''NULL'')
                    WHEN DATA_TYPE IN (''int'', ''bigint'', ''smallint'', ''tinyint'', ''decimal'', ''numeric'', ''float'', ''real'', ''money'', ''smallmoney'')
                    THEN COALESCE(CONVERT(NVARCHAR(MAX), [' + COLUMN_NAME + ']), ''NULL'')
                    WHEN DATA_TYPE = ''bit''
                    THEN COALESCE(CONVERT(NVARCHAR(MAX), [' + COLUMN_NAME + ']), ''NULL'')
                    WHEN DATA_TYPE = ''uniqueidentifier''
                    THEN COALESCE('''''''' + CONVERT(NVARCHAR(MAX), [' + COLUMN_NAME + ']) + ''''''''', ''NULL'')
                    ELSE ''NULL''
                END
            FROM INFORMATION_SCHEMA.COLUMNS c2
            WHERE c2.TABLE_NAME = ''' + @TableName + '''
            ORDER BY c2.ORDINAL_POSITION
            FOR XML PATH('''')
        ), 1, 2, '''') + '');''
    FROM (' + @SelectSQL + ') AS t';

    -- 执行动态SQL
    EXEC sp_executesql @FinalSQL;
END;
GO

-- 使用示例
EXEC sp_GenerateTableInsertScript 'Employees', 10, 'DepartmentID = 1';

六、常见问题解决

1. 处理特殊字符

-- 使用QUOTENAME函数处理单引号
INSERT INTO Products (ProductName, Description)
VALUES (
    'O''Brien''s Product',
    'This contains a single quote: '' and double quote: "'
);

-- 或者使用参数化查询（推荐）
DECLARE @ProductName NVARCHAR(100) = 'O''Brien''s Product';
DECLARE @Description NVARCHAR(500) = 'Special''s description';

INSERT INTO Products (ProductName, Description)
VALUES (@ProductName, @Description);

2. 插入标识列数据

-- 允许插入特定的标识列值
SET IDENTITY_INSERT Employees ON;

INSERT INTO Employees (EmployeeID, FirstName, LastName)
VALUES (1000, '测试', '用户');

SET IDENTITY_INSERT Employees OFF;

-- 获取刚插入的标识值
DECLARE @NewID INT;
INSERT INTO Employees (FirstName, LastName)
VALUES ('New', 'Employee');

SET @NewID = SCOPE_IDENTITY();
-- 或者使用 @@IDENTITY 或 OUTPUT子句

这些脚本和方法涵盖了SQL Server INSERT操作的各个方面，从基础到高级应用，适合不同场景下的数据插入需求。