34,837
社区成员




SELECT @Str=REPLACE(','+@Str,',[','[')
, @Str=REPLACE(REPLACE(REPLACE(@str,',',','''),'[',',('),']',''')')+') AS b([key], [value])'
,@Str=STUFF(@Str,1,1,' SELECT '+RTRIM(@Id)+' AS ID ,[key], [value] FROM (Values')
--PRINT @str
INSERT INTO [dbo].[ItemResume] EXEC( @Str
这一段替换红色,刚没处理字符串引号问题,这里已处理DECLARE @Str VARCHAR(max)='[1, 信息科技],[2,建筑],[3,制造业]'
,@Id INT=10
SELECT @Str=REPLACE(','+@Str,',[','[')
, @Str=REPLACE(REPLACE(REPLACE(@str,',',','''),'[',',('),']',''')')+') AS b([key], [value])'
,@Str=STUFF(@Str,1,1,' SELECT '+RTRIM(@Id)+' AS ID ,[key], [value] FROM (Values')
--PRINT @str
INSERT INTO [dbo].[ItemResume] EXEC( @Str)
SET @Str='Insert t ([Personid],[key],[value]) SELECT '+RTRIM(@Id)+' AS ID ,[key], [value] FROM (Values'+REPLACE(REPLACE(@str,'[','('),']',')')+') AS b([key], [value])'
EXEC( @Str)
方法2
SET @Str='Insert t ( [Personid],[key], [value]) Values'+REPLACE(REPLACE(@str,'[','('+RTRIM(@ID)+','),']',')')
EXEC( @Str)
把红色的替换就行了CREATE proc proc_InsertItemResume
@Id int,@str VARCHAR(max)
as
begin
BEGIN TRAN proc_InsertItemResume
BEGIN TRY
DECLARE @tran_error int;
SET @tran_error = 0;
begin
select @str=@str+','
select @str=charindex(',',@str)
while @str<>0
begin
--执行数据插入
SET @Str='Insert t ([ID],[key],[value]) SELECT '+RTRIM(@Id)+' AS ID ,[key], [value] FROM (Values'+REPLACE(REPLACE(@str,'[','('),']',')')+') AS b([key], [value])'
EXEC( @Str)
end
end
END TRY
BEGIN CATCH
SET @tran_error = @tran_error + 1
END CATCH
IF(@tran_error > 0)
BEGIN
--执行出错,回滚事务
ROLLBACK TRAN;
END
ELSE
BEGIN
--没有异常,提交事务
COMMIT TRAN ;
END
END
SET @Str='Insert t ( [ID],[key], [value]) Values'+REPLACE(REPLACE(@str,'[','('+RTRIM(@ID)+','),']',')')
EXEC( @Str)
DECLARE @Str VARCHAR(max)='[1, 信息科技],[2,建筑],[3,制造业]'
SET @Str='Insert t ( [key], [value]) Values'+REPLACE(REPLACE(@str,'[','('),']',')')
EXEC( @Str)
sql2008以上版本
这样新增数据