一次性插入N条记录时。表里有重复值的话就返回。没有的话就插入
假如表A,字段ID 有
1
2
3
4
4跳记录存在
现在我要插入的值是‘1,3,5,6,7...’(这里传进来的值是VARCHAR型用,隔开)
这样的话5,6,7可以插入。1,3返回
这是表A 的值应该是
1
2
3
4
5
6
7
假如表A,字段ID 有
1
2
3
4
4跳记录存在
现在我要插入的值是‘1,3,5,6,7...’(这里传进来的值是VARCHAR型用,隔开)
这样的话5,6,7可以插入。1,3返回
这是表A 的值应该是
1
2
3
4
5
6
7
(
@inputstr varchar(8000),
@seprator varchar(10)
)
returns @temp table (a varchar(200))
as
begin
declare @i int
set @inputstr = rtrim(ltrim(@inputstr))
set @i = charindex(@seprator, @inputstr)
while @i >= 1
begin
insert @temp values(left(@inputstr, @i - 1))
set @inputstr = substring(@inputstr, @i + 1, len(@inputstr) - @i)
set @i = charindex(@seprator, @inputstr)
end
if @inputstr <> '\'
insert @temp values(@inputstr)
return
end
go
--调用
declare @s varchar(1000)
set @s='1,2,3,4,5,6,7,8,55'
select * from dbo.fn_split(@s,',')
drop function dbo.fn_split
go
create table ta(id int)
insert ta select 1
insert ta select 2
insert ta select 3
insert ta select 4
if object_id('dbo.fn_split')is not null drop function dbo.fn_split
go
create function dbo.fn_split
(
@inputstr varchar(8000),
@seprator varchar(10)
)
returns @temp table (a varchar(200))
as
begin
declare @i int
set @inputstr = rtrim(ltrim(@inputstr))
set @i = charindex(@seprator, @inputstr)
while @i >= 1
begin
insert @temp values(left(@inputstr, @i - 1))
set @inputstr = substring(@inputstr, @i + 1, len(@inputstr) - @i)
set @i = charindex(@seprator, @inputstr)
end
if @inputstr <> '\'
insert @temp values(@inputstr)
return
end
go
--调用
declare @s varchar(1000)
set @s='1,3,5,6,7'
insert ta select a from dbo.fn_split(@s,',') where a not in (select id from ta)
select * from ta
/*id
-----------
1
2
3
4
5
6
7*/
INSERT TB
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4
DECLARE @STR VARCHAR(100)
SET @STR='1,3,5,6,7'
SET @STR='SELECT COL='+REPLACE(@STR,',',' UNION ALL SELECT ')EXEC('INSERT TB '+
' SELECT COL FROM ('+@STR+') T LEFT JOIN TB ON ID=COL WHERE ID IS NULL')
SELECT * FROM TBDROP TABLE TB
/*
ID
-----------
1
2
3
4
5
6
7
*/
insert into A values(1)
insert into A values(2)
insert into A values(3)
insert into A values(4)
go--创建一个函数
create function dbo.fn_split
(
@inputstr varchar(8000),
@seprator varchar(10)
)
returns @temp table (a varchar(200))
as
begin
declare @i int
set @inputstr = rtrim(ltrim(@inputstr))
set @i = charindex(@seprator, @inputstr)
while @i >= 1
begin
insert @temp values(left(@inputstr, @i - 1))
set @inputstr = substring(@inputstr, @i + 1, len(@inputstr) - @i)
set @i = charindex(@seprator, @inputstr)
end
if @inputstr <> '\'
insert @temp values(@inputstr)
return
end
go
--调用
declare @s varchar(50)
set @s = '1,3,5,6,7'insert into a select * from dbo.fn_split(@s,',') where a not in (select id from a)select * from adrop table A
drop function dbo.fn_split /*
ID
-----------
1
2
3
4
5
6
7(所影响的行数为 7 行)*/