sql存储过程处理数组参数
str1,0,0,0,0|str2,0,0,0,0|str3,0,0,0,0| str4,0,0,0,0
要实现把每个 | 分割的字段插入一张表的每个字段，结果是这样
name a1 a2 a3 a4
str1 0 0 0 0
str2 0 0 0 0
str3 0 0 0 0
请教高人赐教！
最新回答
送沵的獨白
2024-10-14 07:10:51
declare @str varchar(500)
declare @insert varchar(8000)
select @insert = 'insert into youTable values '
select @str='str1,1,1,1,1|str2,2,2,2,2|str3,3,3,3,5|str4,4,4,45,4'
select @str=@insert+'('''+@str+''')'
select @str = REPLACE(@str,',',''',''')
select @str = REPLACE(@str,'|',''') '+CHAR(13)+@insert+'(''')
print @str
--exec(@str) 动态执行下生成的语句就好了
--print 结果如下：
insert into youTable values ('str1','1','1','1','1')
insert into youTable values ('str2','2','2','2','2')
insert into youTable values ('str3','3','3','3','5')
insert into youTable values ('str4','4','4','45','4')
狗屎味的小仙女
2024-10-14 07:10:50
--方法二,用字符串拆分
Declare @A varchar(800)='str1,0,0,0|str2,0,0,0|str3,0,0,0'
Set @A='Insert into T Select '''+@A
Set @A=replace(REPLACE(@A,'|',''' union all select '''),',',''',''')+''''
Exec(@A)
/*
Insert into T
Select 'str1','0','0','0'
union all
select 'str2','0','0','0'
union all
select 'str3','0','0','0'
*/
热门标签
