子陌红尘老大来一下~

Question

需要批量输入物品
输入字符串参数ip,用户编号,用户名和物品编号 127.0.0.0,11,大米,12,44;122.0.0.0,22,大豆,33,44,55;128.0.0.0,12,玉米,22,77,88;
用';'区分参数每个用户所属即127.0.0.0,11,大米,12,44
     
         其中用户编号是每个用户所属的第一个数字即11      12      33写入表内为ip  uid name iid
          127.0.0.0 11  大米   12
          127.0.0.0 11  大米   44
          
如果去掉ip呢,怎么写~    写入表内应该是ip  uid name iid
          127.0.0.0 11  大米   12
          127.0.0.0 11  大米   44
刚刚我写错了~

Answer 1

create table test(uid int,name varchar(10),iid varchar(10)) 
gocreate procedure sp_test(@str varchar(1000))
as
begin
    declare @var varchar(1000),@sql varchar(1000)
    while charindex(';',@str)>0
    begin
        set @var=left(@str,charindex(';',@str)-1)
        set @str=stuff(@str,1,charindex(';',@str),'')
        set @var=stuff(@var,1,charindex(',',@var),'')
        set @sql='insert into test(uid,name,iid) select '''+left(@var,charindex(',',@var)-1)+''''
        set @var=stuff(@var,1,charindex(',',@var),'')
        set @sql=@sql+','''+left(@var,charindex(',',@var)-1)+''''
        set @var=stuff(@var,1,charindex(',',@var),'')+','
        while charindex(',',@var)>0
        begin
            declare @s varchar(8000)
            set @s=@sql+','''+left(@var,charindex(',',@var)-1)+''''
            exec(@s)
            set @var=stuff(@var,1,charindex(',',@var),'')
        end
    end
    select * from test
end
goexec sp_test '127.0.0.0,11,大米,12,44;122.0.0.0,22,大豆,33,44,55;128.0.0.0,12,玉米,22,77,88;'
go/*
uid         name       iid        
----------- ---------- ---------- 
11          大米       12
11          大米       44
22          大豆       33
22          大豆       44
22          大豆       55
12          玉米       22
12          玉米       77
12          玉米       88
*/drop procedure sp_test
drop table test

Answer 2

照之前的代码一样的改就行了：
------------------------------------------------------------------------------------------------------------------------
create table test(uid int,name varchar(10),iid varchar(10)) 
gocreate procedure sp_test(@str varchar(1000))
as
begin
    declare @var varchar(1000),@sql varchar(1000)
    while charindex(';',@str)>0
    begin
        set @var=left(@str,charindex(';',@str)-1)
        set @str=stuff(@str,1,charindex(';',@str),'')
        --set @var=stuff(@var,1,charindex(',',@var),'')   注释这一行
        set @sql='insert into test(uid,name,iid) select '''+left(@var,charindex(',',@var)-1)+''''
        set @var=stuff(@var,1,charindex(',',@var),'')
        set @sql=@sql+','''+left(@var,charindex(',',@var)-1)+''''
        set @var=stuff(@var,1,charindex(',',@var),'')+','
        while charindex(',',@var)>0
        begin
            declare @s varchar(8000)
            set @s=@sql+','''+left(@var,charindex(',',@var)-1)+''''
            exec(@s)
            set @var=stuff(@var,1,charindex(',',@var),'')
        end
    end
    select * from test
end
goexec sp_test '11,大米,12,44;22,大豆,33,44,55;12,玉米,22,77,88;'
go/*
uid         name       iid        
----------- ---------- ---------- 
11          大米       12
11          大米       44
22          大豆       33
22          大豆       44
22          大豆       55
12          玉米       22
12          玉米       77
12          玉米       88
*/drop procedure sp_test
drop table test