欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-04-12 15:31:28
插入数据库方法很多,下面Insus.NET列举几种方法,看看你常用的是哪一种: 创建一张表: CREATE TABLE [dbo].[Network_Info] ( [ID] INT NOT NULL, [NAME] NVARCHAR(20) NULL, [MAC] VARCHAR(17) NULL ......

插入数据库方法很多,下面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