📥SQL Server常用语句【插入语句】-习听风雨丨专注WorkFine开发

💡 简记：增(INSERT)删(DELETE)改(UPDATE)查(SELECT)是数据库操作的四大基础功能，简称CRUD（Create, Read, Update, Delete）。

本文目录 显示

增 (INSERT)

作用：向表中添加新记录
示例：INSERT INTO 表名 (字段) VALUES (值)
用途：添加新数据，如新增订单、注册用户等

1. 基础单条插入

USE [数据库名];
GO

INSERT INTO [dbo].[员工表] (
    [员工编号],
    [姓名],
    [部门],
    [入职日期]
)
VALUES (
    'E1001',
    '张三',
    '技术部',
    '2023-06-15'
);
GO

2. 批量多行插入

USE [数据库名];
GO

INSERT INTO [dbo].[产品表] (
    [产品编号],
    [产品名称],
    [单价]
)
VALUES 
    ('P1001', '笔记本电脑', 5999.00),
    ('P1002', '无线鼠标', 99.00),
    ('P1003', '机械键盘', 299.00);
GO

3. 查询结果插入（INSERT SELECT）

USE [数据库名];
GO

-- 将3月订单插入季度汇总表
INSERT INTO [dbo].[季度订单汇总表] (
    [订单编号],
    [客户编号],
    [订单金额],
    [下单时间]
)
SELECT 
    [订单编号],
    [客户编号],
    [订单金额],
    [下单时间]
FROM [dbo].[订单表]
WHERE [下单时间] BETWEEN '2023-03-01' AND '2023-03-31'
AND [订单状态] = '已完成';
GO

4. 条件插入（不存在才插入）

USE [数据库名];
GO

-- 只插入不存在的客户
INSERT INTO [dbo].[客户表] (
    [客户编号],
    [客户名称],
    [联系电话]
)
SELECT 
    [临时编号],
    [临时名称],
    [临时电话]
FROM [dbo].[临时客户表] t
WHERE NOT EXISTS (
    SELECT 1 
    FROM [dbo].[客户表] c 
    WHERE c.[客户编号] = t.[临时编号]
);
GO

5. 带计算的插入

USE [数据库名];
GO

-- 插入计算后的销售统计
INSERT INTO [dbo].[销售月报表] (
    [月份],
    [销售员ID],
    [销售额],
    [订单数]
)
SELECT 
    FORMAT([下单时间], 'yyyy-MM') AS [月份],
    [销售员ID],
    SUM([订单金额]) AS [销售额],
    COUNT(*) AS [订单数]
FROM [dbo].[订单表]
WHERE [下单时间] BETWEEN '2023-01-01' AND '2023-01-31'
GROUP BY 
    FORMAT([下单时间], 'yyyy-MM'),
    [销售员ID];
GO

6. 带JOIN的查询结果插入

USE [数据库名];
GO

-- 关联多表插入
INSERT INTO [dbo].[客户订单汇总] (
    [客户编号],
    [客户名称],
    [订单总数],
    [总消费金额]
)
SELECT 
    c.[客户编号],
    c.[客户名称],
    COUNT(o.[订单编号]) AS [订单总数],
    SUM(o.[订单金额]) AS [总消费金额]
FROM [dbo].[客户表] c
LEFT JOIN [dbo].[订单表] o ON c.[客户编号] = o.[客户编号]
GROUP BY 
    c.[客户编号],
    c.[客户名称];
GO

7. 带分页的查询结果插入

USE [数据库名];
GO

-- 分批插入大数据量
INSERT INTO [dbo].[归档订单表] (
    [订单编号],
    [客户编号],
    [订单金额]
)
SELECT 
    [订单编号],
    [客户编号],
    [订单金额]
FROM [dbo].[订单表]
ORDER BY [下单时间]
OFFSET 0 ROWS FETCH NEXT 1000 ROWS ONLY;
GO