字符串分割后的值，把表中不存在的插入表中 --供大家参考

使用场景，自行思考……

--创建表tb1Create table tb1(cola int,colb varchar(50))--插入数据insert into tb1(cola,colb)select 1, 'A' union allselect 2, 'B' union allselect 3, 'C';--存储过程Create proc sp_tbTest@sid int,--ID@str varchar(20)--A,B,C,D,GASBEGINinsert into tb1(cola,colb) select @sid ,sp from [dbo].[split](@str,',')where sp not in (select colb from tb1 where cola=@sid)ENDexec sp_tbTest 4,'D,G,A,B,C';--表中已近存在了A，B，C，执行存储过程的话，本次插入的是D,Gselect * from tb1

--实现分割的函数

ALTER function [dbo].[split](@SourceSql varchar(8000),@Code varchar(10))returns @temp table(sp varchar(1000))--实现split功能 的函数--date :2007-7-10--Author :spasbegindeclare @i intset @SourceSql=rtrim(ltrim(@SourceSql))set @i=charindex(@Code,@SourceSql)while @i>=1begininsert @temp values(left(@SourceSql,@i-1))set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)set @i=charindex(@Code,@SourceSql)endif @SourceSql<>'/'insert @temp values(@SourceSql)returnend

--以下是朋友Lewis写的案例;没有用到自定义的split函数，而是直接在存储过程中分割字符串的。

create table tb_test(shop varchar(10))insert tb_test values('a')alter PROCEDURE [dbo].sp_TEST@strShopID varchar(1000)=''ASBEGINSET NOCOUNT ON;declare @tbShop table(shopid varchar(32))--declare @tbTopShop table(shopid varchar(32),Num int)set @strShopID=@strShopID+','while(len(@strShopID)>1)beginif left(@strShopID,1)=','set @strShopID=substring(@strShopID,2,len(@strShopID))insert @tbShopselect substring(@strShopID,1,charindex( ',',@strShopID)-1)set @strShopID=substring(@strShopID,charindex( ',',@strShopID),len(@strShopID))endinsert tb_testselect * from @tbshop where shopid not in(select * from tb_test)ENDsp_TEST 'a,b,c,d,e,f'select * from tb_test

博客：http://www.haoyuncn.net/sql-split-insert