sqlserver根据id集合，批量插入。（巧用sqlserver内置函数）

 

场景如下，传入的id，如1,3,4,88。可以在.net后台处理，但是我更习惯在数据库中操作。

插入数据时可以这样处理，直接贴代码。

 CREATE PROCEDURE pro_CategorySave
    (
      @ids VARCHAR(400) ,
      @type INT ,
      @TemplateID INT ,
      @CategoryID INT
        
    )
  AS 
    BEGIN
        DECLARE @str VARCHAR(8000) ;
        DELETE  FROM GaituApp.dbo.TemplateRelationship
        WHERE   TemplateID = @TemplateID
                AND Type = @type
                AND CategoryID = @CategoryID
        --判断ids是否为空，为空则不插入
        IF ( @ids <> '' ) 
            BEGIN
                SET @str = 'INSERT INTO GaituApp.dbo.TemplateRelationship
        ( TemplateID ,          
          Type ,
          CategoryID ,
          CreateTime,
          Pid
        )
        SELECT ' + CAST(@TemplateID AS VARCHAR(20)) + ','
                    + CAST(@type AS VARCHAR(20)) + ','
                    + CAST(@CategoryID AS VARCHAR(20)) + ',GETDATE(),'
                    + REPLACE(@ids, ',',
                              ' union select '
                              + CAST(@TemplateID AS VARCHAR(20)) + ','
                              + CAST(@type AS VARCHAR(20)) + ','
                              + CAST(@CategoryID AS VARCHAR(20))
                              + ',GETDATE(),')
        
                --PRINT @str
        
        EXEC (@str)
            END        
    END

 

　　思路就是利用sqlserver自带的replace方法将分隔符替换成union select.具体情况具体分析。

技巧二：sqlserver“数组”使用。

在sqlserver中执行批量操作时，可同时处理多个数据，由于sql不支持数组，可以变向处理，如下：

1.利用replace，直接贴代码，

CREATE TABLE #temp
(
    STR VARCHAR(20) NOT NULL
)
DECLARE @str VARCHAR(200)
DECLARE @result VARCHAR(1000)
SET @str='a,b,c,d,e,f,g'
SET @result='INSERT INTO #temp
        ( STR ) select '''+REPLACE(@str,',',''' union select ''')+''''
        PRINT @result
   EXEC(@result)
   SELECT * FROM #temp     
DROP TABLE #temp

--print result
INSERT INTO #temp
        ( STR ) select 'a' union select 'b' union select 'c' union select 'd' union select 'e' union select 'f' union select 'g'

 2.利用charindex结合substring。

charindex用法，charindex('要查找的分隔符','字符串',int_length),

substring用法，substring('字符串',int_start,int_length).贴代码咯，详细代码注释放在程序段了

CREATE TABLE #temp
(
    strcolumn VARCHAR(20)
)
DECLARE @str VARCHAR(200);
--当前的字符串索引
DECLARE @currPostion INT;
--当前的分割符索引
DECLARE @currSplit INT;
SET @str='i,love,you,lover';
--初始化，索引都为1，数据库函数中，索引都是以1开始。
SET @currPostion=1;
SET @currSplit=1;
WHILE @currPostion<LEN(@str)
BEGIN
    SET @currSplit=CHARINDEX(',',@str,@currPostion);
    IF @currSplit>@currPostion
    BEGIN
        INSERT INTO #temp
                ( strcolumn )
        SELECT SUBSTRING(@str,@currPostion,@currSplit-@currPostion);
    END
    ELSE
    BEGIN
        INSERT INTO #temp
                ( strcolumn )
        SELECT SUBSTRING(@str,@currPostion,LEN(@str)-@currPostion+1);
        BREAK;
    END
    SET @currPostion=@currSplit+1;
END
SELECT * FROM #temp
DROP TABLE #temp 

 下面贴个利用charindex,substring获取数组长度的函数。注释在代码中。

CREATE FUNCTION [dbo].[returnArryLength]
    (
      @str VARCHAR(5000) ,
      @split VARCHAR(10)
    )
RETURNS INT
AS 
    BEGIN
    --当前字符起始位置
        DECLARE @curr_position INT ;
    --当前分隔符位置
        DECLARE @curr_split INT ;
        DECLARE @count INT ;
        SET @curr_position = 1 ;
        SET @curr_split = 1 ;
        SET @str = LTRIM(RTRIM(@str)) ;
        IF LEN(@str) = 0 
            BEGIN
                RETURN 0 ;
            END
        ELSE 
            BEGIN
                SET @count=1;
                SET @curr_split=CHARINDEX(@split,@str,@curr_position)
                WHILE @curr_split<>0
                BEGIN
                    SET @count=@count+1;
                    SET @curr_position=@curr_split+1;
                    SET @curr_split=CHARINDEX(@split,@str,@curr_position);
                END
            END
        RETURN @count ;
    END

然后是利用charindex,substring根据索引返回元素的函数。注释在代码中。

CREATE FUNCTION [dbo].[getArryElementByIndex]
(
    @str varchar(5000),
    @split varchar(20),
    @index INT--根据索引查询元素
)
RETURNS VARCHAR(500)
AS
BEGIN
    --a,b,c,d,e
    SET @str=LTRIM(RTRIM(@str));
    DECLARE @loopcount INT;
    DECLARE @i INT;
    DECLARE @returnElement VARCHAR(50);
    --分割符索引
    DECLARE @curr_split INT;
    --当前字符索引
    DECLARE @curr_positon INT;
    --设置循环次数
    SET @loopcount=@index;
    SET @i=1;
    SET @curr_split=1;
    SET @curr_positon=1;
    WHILE @i<=@loopcount
    BEGIN
        SET @curr_split=CHARINDEX(@split,@str,@curr_positon);
        IF @curr_split<>0
        BEGIN
            SET @returnElement=SUBSTRING(@str,@curr_positon,@curr_split-@curr_positon)
        END
        ELSE
        BEGIN
            SET @returnElement=SUBSTRING(@str,@curr_positon,LEN(@str)-@curr_positon+1)
        END
        SET @curr_positon=@curr_split+1;
        SET @i=@i+1;
    END
    RETURN @returnElement;
END