从字符串向表中插入行 [英] insert rows in to table from string

问题描述

我有一个字符串''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

推荐答案

没有拆分功能，因此您不能直接在SQL Server中执行此操作.

如需一般帮助，请尝试
http://www.tek-tips.com/viewthread.cfm?qid=1685247 [ ^ ]
http://social.msdn.microsoft.com/Forums/zh/transactsql/thread/aeaf45df-b44e-40b5-abd0-dcf6d5953e5f [

There is no split function and hence you cannot do this directly in SQL Server.

For general assistance, try
http://www.tek-tips.com/viewthread.cfm?qid=1685247[^]
http://social.msdn.microsoft.com/Forums/en/transactsql/thread/aeaf45df-b44e-40b5-abd0-dcf6d5953e5f[^]

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..)

尝试此操作.但是我在这里使用循环

try this. But am using loop here

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

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