SQL Server数据插入 #

INSERT [INTO] table_name [(column_list)]
VALUES (value_list) [, (value_list) ...];

-- 或
INSERT [INTO] table_name [(column_list)]
SELECT column_list FROM source_table;

-- 创建示例表
CREATE TABLE users (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name NVARCHAR(50) NOT NULL,
    email VARCHAR(100),
    age INT,
    status BIT DEFAULT 1,
    created_at DATETIME2 DEFAULT SYSDATETIME()
);

CREATE TABLE orders (
    id INT IDENTITY(1,1) PRIMARY KEY,
    user_id INT,
    order_date DATE DEFAULT GETDATE(),
    amount DECIMAL(10,2),
    status NVARCHAR(20) DEFAULT 'pending'
);

-- 插入所有列
INSERT INTO users (name, email, age, status)
VALUES ('John', 'john@example.com', 25, 1);

-- 插入部分列（其他使用默认值或NULL）
INSERT INTO users (name, email)
VALUES ('Jane', 'jane@example.com');

-- 省略列名（按顺序插入所有列，不推荐）
INSERT INTO users
VALUES ('Bob', 'bob@example.com', 28, 0, DEFAULT);

-- 使用DEFAULT关键字
INSERT INTO users (name, email, age, status, created_at)
VALUES ('Alice', 'alice@example.com', 22, DEFAULT, DEFAULT);

-- 省略有默认值的列
INSERT INTO users (name, email, age)
VALUES ('Charlie', 'charlie@example.com', 35);

-- 显式插入NULL
INSERT INTO users (name, email, age, status)
VALUES ('David', NULL, 30, 1);

-- 省略允许NULL的列
INSERT INTO users (name, age)
VALUES ('Eve', 28);

-- 一次插入多行
INSERT INTO users (name, email, age, status)
VALUES 
    ('User1', 'user1@example.com', 25, 1),
    ('User2', 'user2@example.com', 30, 1),
    ('User3', 'user3@example.com', 28, 0),
    ('User4', 'user4@example.com', 22, 1),
    ('User5', 'user5@example.com', 35, 0);

-- 最多支持1000行

-- 使用UNION ALL插入多行
INSERT INTO users (name, email, age)
SELECT 'UserA', 'usera@example.com', 25
UNION ALL SELECT 'UserB', 'userb@example.com', 30
UNION ALL SELECT 'UserC', 'userc@example.com', 28;

-- 创建新表并插入数据
SELECT * INTO users_backup
FROM users;

-- 创建空表结构
SELECT * INTO users_empty
FROM users
WHERE 1 = 0;

-- 选择部分列
SELECT id, name, email INTO users_simple
FROM users
WHERE status = 1;

-- 从多表查询创建新表
SELECT 
    u.id,
    u.name,
    o.order_date,
    o.amount
INTO user_orders
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- 从另一个表插入
INSERT INTO users_backup (name, email, age, status)
SELECT name, email, age, status
FROM users
WHERE status = 1;

-- 从多表查询插入
INSERT INTO user_summary (user_id, user_name, total_orders)
SELECT 
    u.id,
    u.name,
    COUNT(o.id)
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

-- 只插入不存在的数据
INSERT INTO users (name, email, age)
SELECT name, email, age
FROM new_users n
WHERE NOT EXISTS (
    SELECT 1 FROM users u WHERE u.email = n.email
);

-- 使用EXCEPT
INSERT INTO users (name, email, age)
SELECT name, email, age FROM new_users
EXCEPT
SELECT name, email, age FROM users;

-- 从CSV文件批量插入
BULK INSERT users
FROM 'D:\Data\users.csv'
WITH (
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
    FIRSTROW = 2,           -- 跳过标题行
    TABLOCK
);

-- 完整选项
BULK INSERT orders
FROM 'D:\Data\orders.txt'
WITH (
    FIELDTERMINATOR = '|',
    ROWTERMINATOR = '0x0a',
    FIRSTROW = 1,
    LASTROW = 1000,
    MAXERRORS = 10,
    ERRORFILE = 'D:\Data\errors.txt',
    CODEPAGE = '65001',     -- UTF-8
    DATAFILETYPE = 'char',
    TABLOCK,
    CHECK_CONSTRAINTS
);

-- 使用OPENROWSET读取文件
INSERT INTO users (name, email, age)
SELECT name, email, age
FROM OPENROWSET(
    BULK 'D:\Data\users.csv',
    FORMATFILE = 'D:\Data\users_format.xml',
    FIRSTROW = 2
) AS t;

-- 直接查询文件
SELECT * FROM OPENROWSET(
    BULK 'D:\Data\users.csv',
    SINGLE_CLOB
) AS contents;

<!-- users_format.xml -->
<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="50"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\r\n"/>
</RECORD>
<ROW>
  <COLUMN SOURCE="1" NAME="name" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="2" NAME="email" xsi:type="SQLVARCHAR"/>
  <COLUMN SOURCE="3" NAME="age" xsi:type="SQLINT"/>
</ROW>
</BCPFORMAT>

-- 创建表类型
CREATE TYPE UserType AS TABLE (
    name NVARCHAR(50),
    email VARCHAR(100),
    age INT
);

-- 创建存储过程
CREATE PROCEDURE usp_InsertUsers
    @users UserType READONLY
AS
BEGIN
    INSERT INTO users (name, email, age)
    SELECT name, email, age FROM @users;
END
GO

-- 调用存储过程
DECLARE @newUsers AS UserType;
INSERT INTO @newUsers VALUES 
    ('UserX', 'userx@example.com', 25),
    ('UserY', 'usery@example.com', 30);

EXEC usp_InsertUsers @newUsers;

-- 返回插入的数据
INSERT INTO users (name, email, age)
OUTPUT inserted.id, inserted.name, inserted.email
VALUES ('Test', 'test@example.com', 25);

-- 输出结果
-- id | name | email
-- 10 | Test | test@example.com

-- 创建审计表
CREATE TABLE users_audit (
    action NVARCHAR(10),
    id INT,
    name NVARCHAR(50),
    action_time DATETIME DEFAULT GETDATE()
);

-- 插入并记录到审计表
INSERT INTO users (name, email, age)
OUTPUT 'INSERT', inserted.id, inserted.name INTO users_audit
VALUES ('AuditTest', 'audit@example.com', 30);

-- 批量插入并输出
INSERT INTO users (name, email, age)
OUTPUT inserted.id, inserted.name, inserted.email
VALUES 
    ('Multi1', 'multi1@example.com', 25),
    ('Multi2', 'multi2@example.com', 30),
    ('Multi3', 'multi3@example.com', 28);

-- 插入后获取标识值
INSERT INTO users (name, email) VALUES ('NewUser', 'new@example.com');
SELECT SCOPE_IDENTITY() AS [NewID];

-- 使用OUTPUT获取
INSERT INTO users (name, email)
OUTPUT inserted.id
VALUES ('NewUser2', 'new2@example.com');

-- 启用标识插入
SET IDENTITY_INSERT users ON;

INSERT INTO users (id, name, email)
VALUES (1000, 'Special', 'special@example.com');

SET IDENTITY_INSERT users OFF;

-- 使用内置函数
INSERT INTO users (name, email, created_at)
VALUES ('FuncTest', 'func@example.com', GETDATE());

-- 使用NEWID()
INSERT INTO sessions (id, user_id)
VALUES (NEWID(), 1);

-- 使用序列
INSERT INTO users (id, name)
VALUES (NEXT VALUE FOR seq_user_id, 'SeqUser');

-- 使用计算表达式
INSERT INTO products (name, price, discounted_price)
VALUES ('Product', 100.00, 100.00 * 0.9);

-- 使用CASE
INSERT INTO users (name, status)
VALUES ('Test', CASE WHEN 1=1 THEN 1 ELSE 0 END);

-- 使用子查询
INSERT INTO user_stats (user_id, order_count)
SELECT 
    u.id,
    (SELECT COUNT(*) FROM orders WHERE user_id = u.id)
FROM users u;

-- 使用TABLOCK提示
INSERT INTO users WITH (TABLOCK) (name, email, age)
SELECT name, email, age FROM staging_users;

-- 禁用约束和索引（大数据量）
ALTER TABLE users NOCHECK CONSTRAINT ALL;
-- 执行批量插入
ALTER TABLE users CHECK CONSTRAINT ALL;

-- 使用最小日志模式
-- 在简单或大容量日志恢复模式下

-- 先导入临时表
SELECT * INTO #temp_users
FROM OPENROWSET(BULK 'D:\Data\users.csv', FORMATFILE = 'D:\Data\format.xml') AS t;

-- 再插入目标表
INSERT INTO users (name, email, age)
SELECT name, email, age FROM #temp_users;

DROP TABLE #temp_users;

BEGIN TRY
    INSERT INTO users (name, email)
    VALUES ('Test', 'test@example.com');
    
    PRINT '插入成功';
END TRY
BEGIN CATCH
    PRINT '插入失败: ' + ERROR_MESSAGE();
    
    -- 可以选择回滚事务
END CATCH

-- 使用INSERT...SELECT过滤错误数据
BEGIN TRY
    INSERT INTO users (name, email, age)
    SELECT name, email, age
    FROM source_users
    WHERE age BETWEEN 0 AND 150;  -- 过滤不满足约束的数据
END TRY
BEGIN CATCH
    PRINT '部分数据插入失败';
END CATCH

-- 使用@@ROWCOUNT
INSERT INTO users (name, email)
VALUES ('Test', 'test@example.com');

IF @@ROWCOUNT > 0
    PRINT '插入成功，影响 ' + CAST(@@ROWCOUNT AS VARCHAR) + ' 行';
ELSE
    PRINT '插入失败';

-- 插入后验证
DECLARE @name NVARCHAR(50) = 'VerifyTest';
DECLARE @email VARCHAR(100) = 'verify@example.com';

INSERT INTO users (name, email) VALUES (@name, @email);

IF EXISTS (SELECT 1 FROM users WHERE name = @name AND email = @email)
    PRINT '数据验证成功';
ELSE
    PRINT '数据验证失败';