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

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

创建一张表：

create table [dbo].[network_info]
(
    [id] int not null,
    [name] nvarchar(20) null,
    [mac] varchar(17) null,
    [ipv4] varchar(15) null
)

方法一：

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

方法二：

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

方法三：

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

方法四：

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

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

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

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

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

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

相关推荐