我现在有个表1字段为
NAME ID
我存储过程传进来的2个参数都是VARCHAR型的
假如我进来的值是@NAME='A' ID='1,2,3,4'
我要往表1查入
A 1
A 2
A 3
A 4
条件是如果ID有重复的就不要插进去
请问这个SQL怎么写啊
NAME ID
我存储过程传进来的2个参数都是VARCHAR型的
假如我进来的值是@NAME='A' ID='1,2,3,4'
我要往表1查入
A 1
A 2
A 3
A 4
条件是如果ID有重复的就不要插进去
请问这个SQL怎么写啊
而且ID字段一般都是作为主键而定义的,这样就是唯一的了
@Name VARCHAR(20),
@ID VARCHAR(100)
AS
DECLARE @t TABLE(id int identity,flag bit);
INSERT @t (flag)
SELECT TOP 100 0
FROM sysobjects; DECLARE @temp TABLE(seq int identity,ID varchar(50)); INSERT @temp(ID)
SELECT
SUBSTRING(@ID,A.ID,CHARINDEX(',',@ID+',',A.ID)-A.ID) AS ID
FROM @t AS A
WHERE SUBSTRING(','+@ID,A.ID,1)=','; IF EXISTS(SELECT *
FROM @temp AS A
WHERE EXISTS(
SELECT 1
FROM @temp
WHERE ID=A.ID AND seq<>A.seq
)
)
BEGIN
RAISERROR('不能插入数据!',16,1);
RETURN;
END
ELSE
INSERT tb1(Name,ID)
SELECT @Name,ID
FROM @temp;GO
set @NAME='A'
set @ID='1,2,3,4'
declare @sql varchar(100)
select @sql='insert tb select '''+@NAME+''', '+replace(@ID,',',' union all select '''+@NAME+''', ')
exec (@sql)select * from tb/**
NAME ID
---------- -----------
A 1
A 2
A 3
A 4(所影响的行数为 4 行)
**/
GOCREATE PROC p
@Name VARCHAR(20),
@ID VARCHAR(100)
AS
DECLARE @t TABLE(id int identity,flag bit);
INSERT @t (flag)
SELECT TOP 100 0
FROM sysobjects; DECLARE @temp TABLE(seq int identity,ID varchar(50)); INSERT @temp(ID)
SELECT
SUBSTRING(@ID,A.ID,CHARINDEX(',',@ID+',',A.ID)-A.ID) AS ID
FROM @t AS A
WHERE SUBSTRING(','+@ID,A.ID,1)=','; IF EXISTS(SELECT *
FROM @temp AS A
WHERE EXISTS(
SELECT 1
FROM @temp
WHERE ID=A.ID AND seq<>A.seq
)
)
BEGIN
RAISERROR('不能插入数据!',16,1);
RETURN;
END
ELSE
INSERT tb1(Name,ID)
SELECT @Name,ID
FROM @temp;GODECLARE @Name VARCHAR(20),@ID VARCHAR(100);
SELECT @Name='A',@ID='1,2,3,4'EXEC p @Name,@IDSELECT @Name='B',@ID='5,6,7,8'EXEC p @Name,@IDSELECT @Name='C',@ID='5,5,7,8'EXEC p @Name,@ID
SELECT * FROM tb1
GO
DROP TABLE tb1
DROP PROC p/*
消息 50000,级别 16,状态 1,过程 p,第 28 行
不能插入数据!
Name ID
-------------------- --------------------
A 1
A 2
A 3
A 4
B 5
B 6
B 7
B 8(8 行受影响)
*/
set @NAME='A'
set @ID='1,2,3,4,2,3,5'
declare @sql varchar(1000)
select @sql='insert tb select distinct * from (select '''+@NAME+''' as name, '+replace(@ID,',',' as id union all select '''+@NAME+''', ')+') t'
print @sql
exec (@sql)select * from tb/**
NAME ID
---------- -----------
A 1
A 2
A 3
A 4
A 5(所影响的行数为 5 行)
**/
你的重复是tb1表里的ID不能重复..还是参数@ID里的值不能重复?
GOCREATE PROC p
@Name VARCHAR(20),
@ID VARCHAR(100)
AS
DECLARE @t TABLE(id int identity,flag bit);
INSERT @t (flag)
SELECT TOP 100 0
FROM sysobjects; DECLARE @temp TABLE(seq int identity,ID varchar(50)); INSERT @temp(ID)
SELECT
SUBSTRING(@ID,A.ID,CHARINDEX(',',@ID+',',A.ID)-A.ID) AS ID
FROM @t AS A
WHERE SUBSTRING(','+@ID,A.ID,1)=','; INSERT tb1
SELECT @Name,ID
FROM @temp AS A
WHERE NOT EXISTS(
SELECT *
FROM tb1
WHERE Name=@Name AND ID=A.ID
)GODECLARE @Name VARCHAR(20),@ID VARCHAR(100);
SELECT @Name='A',@ID='1,2,3,4'EXEC p @Name,@IDSELECT @Name='A',@ID='5,3,4,8'EXEC p @Name,@IDSELECT @Name='C',@ID='5,5,7,8'EXEC p @Name,@ID
SELECT * FROM tb1
GO
DROP TABLE tb1
DROP PROC p/*
Name ID
-------------------- --------------------
A 1
A 2
A 3
A 4
A 5
A 8
C 5
C 5
C 7
C 8(10 行受影响)
*/
例如
A 1
A 2
A 3
B 1
B 3
以上的值是可以的
如果你再想插入
A 1或者B 1的话就不能插了。。A 4的话就可以。。举个例子而已
你是指本次有重复的不插入,还是表中已有重复的不插入?
select * from tb
/**
NAME ID
---------- -----------
A 1
A 2
A 3
A 4
A 5(所影响的行数为 5 行)
**/
--再加一个判断
declare @NAME VARCHAR(10),@ID VARCHAR(100)
set @NAME='A'
set @ID='1,2,3,4,5,6,7,8'
declare @sql varchar(1000)
select @sql='insert tb select distinct * from (select '''+@NAME+''' as name, '+replace(@ID,',',' as id union all select '''+@NAME+''', ')+') t where not exists(select * from tb where id=t.id)'
exec (@sql)select * from tb/**
NAME ID
---------- -----------
A 1
A 2
A 3
A 4
A 5
A 6
A 7
A 8(所影响的行数为 8 行)
**/
B 1
B 2
B 3
B 4
B 5
B 6
B 7
B 8
这样的数据就插不进去啊
set @NAME='B'
set @ID='1,2,3,4,5,6,7,8'
declare @sql varchar(1000)
select @sql='insert tb select distinct * from (select '''+@NAME+''' as name, '+replace(@ID,',',' as id union all select '''+@NAME+''', ')+') t where not exists(select * from tb where id=t.id)'
exec (@sql)select * from tb
(0 行受影响)
set @NAME='B'
set @ID='1,2,3,4,5,6,7,8'
declare @sql varchar(1000)
select @sql='insert tb select distinct * from (select '''+@NAME+''' as name, '+replace(@ID,',',' as id union all select '''+@NAME+''', ')+') t where not exists(select * from tb where id=t.id and name=t.name)'
exec (@sql)
set @NAME='B'
set @ID='1,2,3,4,5,6,7,8'
declare @sql varchar(1000)
select @sql='insert tb select distinct * from (select '''+@NAME+''' as name, '+replace(@ID,',',' as id union all select '''+@NAME+''', ')+') t where not exists(select * from tb where id=t.id and name=t.name)'
exec (@sql) 我想多插入2列值怎么搞啊。分别是0和1
declare @NAME VARCHAR(10),@ID VARCHAR(100)
set @NAME='B'
set @ID='1,2,3,4,5,6,7,8'
declare @sql varchar(1000)
select @sql='insert tb select distinct t.*,0,1 from (select '''+@NAME+''' as name, '+replace(@ID,',',' as id union all select '''+@NAME+''', ')+') t where not exists(select * from tb where id=t.id and name=t.name)'
exec (@sql)