从字符串向表中插入行

本文介绍了从字符串向表中插入行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有一个字符串''1,2,3,4,5,88,55,77,56''

和一个表Msttemp(id int)

现在我想将上述字符串中的数据插入表中.

我正在使用sql server 2005，并且不想使用循环.

问候，
Sushil

Hi,

i have a string ''1,2,3,4,5,88,55,77,56''

and a table Msttemp(id int)

now i want to insert the data in to table from the above string.

i am using sql server 2005 and do not want to use loop.

Regards,
Sushil

推荐答案

string str = "12,344,3";
string[] vl= str.Split(',');

现在，您可以从"vl"中选择值，例如vl [0]，vl [1]等，并将它们以正常情况插入表中...
(尝试将类型转换以与数据库表中指定的类型匹配..)

now you can select values from ''vl'' as vl[0],vl[1] etc and insert them to table as normal case...
(try type conversion to match with type specified in database table..)

CREATE PROCEDURE asp_splitstrings
(@string varchar(50))
 AS 
 DECLARE @id integer = 1,
  @previous_id integer = 0,
   @value varchar(50)

     WHILE @id > 0 
     BEGIN
      SET @id = CHARINDEX(',',@string,@previous_id+1) 
      IF @id > 0 
      BEGIN 
      SET @value = SUBSTRING(@string,@previous_id+1,@id-@previous_id-1) 
      INSERT INTO [Msttemp] VALUES (@value) 
      SET @previous_id = @id 
      END 
      END 
      IF @previous_id < LEN(@string) 
      BEGIN 
      SET @value = SUBSTRING(@string,@previous_id+1,LEN(@string))
       INSERT INTO [Msttemp] VALUES (@value) 
       END
  
        
exec asp_splitstrings '1,2,3,4,5,88,55,77,56'
        
 select * from Msttemp

这篇关于从字符串向表中插入行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

Vl