在一个批次中插入多笔记录的几种方法

来源：cnblogs　　作者：Insus.NET　　时间：2019/5/27 8:46:57　　对本文有异议

插入数据库方法很多，下面Insus.NET列举几种方法，看看你常用的是哪一种：

创建一张表：

CREATE TABLE [dbo].[Network_Info]
(
    [ID] INT NOT NULL,
    [NAME] NVARCHAR(20) NULL,
    [MAC] VARCHAR(17) NULL,
    [IPv4] VARCHAR(15) NULL
)

Source Code

方法一：

INSERT INTO [dbo].[Network_Info] ([ID],[NAME],[MAC],[IPv4])
VALUES 
    (11,'IT00003','48-A3-80-78-CC-EC','10.0.0.33'),
    (12,'','44-C3-46-E0-CA-6D','192.168.3.22'),
    (13,'IT06531','EC-01-EE-2E-92-BE','127.0.0.1')
GO

Source Code

方法二：

INSERT INTO [dbo].[Network_Info] ([ID],[NAME],[MAC],[IPv4])
SELECT [ID],[NAME],[MAC],[IPv4] FROM (
VALUES
    (76,'','E4-46-DA-E8-0F-31','10.8.8.6'),
    (35,'IT23450','60-91-F3-A7-3E-4B','10.0.3.56'),
    (39,'IT45673','30-84-54-9D-18-2F','192.168.0.55')
) AS T([ID],[NAME],[MAC],[IPv4])
GO

Source Code

方法三：

INSERT [dbo].[Network_Info] ([ID],[NAME],[MAC],[IPv4])
EXECUTE ('SELECT 87,''IT00453'',''F4-46-DA-E8-0F-31'',''''
          SELECT 65,''IT00876'',''E6-16-DA-E8-0F-31'',''192.168.0.200''
          SELECT 34,'''',''E4-46-DA-E8-0F-31'','''''
        )
GO

Source Code

方法四：

INSERT [dbo].[Network_Info] ([ID],[NAME],[MAC],[IPv4])
SELECT 77,'IT23453','C8-28-B3-6A-E9-36','10.2.0.67'
UNION
SELECT 78,'IT11111','8E-28-B3-6A-E9-36','127.0.0.1'
UNION
SELECT 79,'IT11112','AB-28-B3-6A-E9-36','10.7.0.23'
GO

Source Code

方法五重复单笔插入方法：

INSERT INTO [dbo].[Network_Info] ([ID],[NAME],[MAC],[IPv4])
VALUES (99,'IT76003','48-A3-80-78-CC-EC','10.4.4.4')
INSERT INTO [dbo].[Network_Info] ([ID],[NAME],[MAC],[IPv4])
VALUES (84,'IT98004','44-C3-46-E0-CA-6D','192.168.3.22')
INSERT INTO [dbo].[Network_Info] ([ID],[NAME],[MAC],[IPv4])
VALUES (81,'IT43431','EC-01-EE-2E-92-BE','192.0.0.1')
GO

Source Code

最后一种方法，是目标表不存在，在插入数据时创建：

SELECT 16 AS [ID],'IT43420' AS [NAME],'30-84-54-9D-18-2F' AS [MAC],'192.168.0.55' AS [IPv4]
INTO  [dbo].[Network_Info_1]  --这种方法是目标表不存在
UNION
SELECT 19,'','88-28-B3-6A-E9-36','10.6.0.43'
UNION
SELECT 45,'IT34078','88-28-B3-6A-E9-36',''
UNION
SELECT 32,'IT23043','88-28-B3-6A-E9-36','10.6.0.43'
GO