如何将字符串数据按行插入到临时表中 [英] how to insert string data into a temp table rowwise

问题描述

如何将字符串数据插入表变量中

例如:string =''41,21,23,25''

我想将上述ID插入表变量中，如下所示

ID
41
21
23
25

解决方案

 创建  FUNCTION  DBO.FUN_SPLIT
(
 @ DATA   VARCHAR ( 2000 )，
 @ SEP   VARCHAR ( 5 )
)
返回  @ TEMP   TABLE (ID  INT   IDENTITY ( 1 ， NVARCHAR ( AS 
/*   ************************ ****************************************************** ***********测试:选择*来自DBO.FUN_SPLIT('A，B，C'，'，')
****************************************************** ****************************************/
开始
声明  @ CNT   INT 
 SET   @ CNT  =  1 
白色(CHARINDEX( @ SEP ， @ DATA )> 0)
开始
插入  INTO   @ TEMP (数据)
选择 DATA = LTRIM(RTRIM(SUBSTRING( @ DATA ， 1 ，CHARINDEX( @ SEP ， @ DATA )-1))))
 SET   @ DATA  = SUBSTRING( @ DATA  ，CHARINDEX( @ SEP ， @ DATA )+ 1，LEN( > @DATA ))
 SET   @ CNT  =  @ CNT  +  1 
 END 
插入  INTO   @ TEMP (数据)
选择 DATA = LTRIM(RTRIM( @ DATA ))
返回 

END



希望对您有帮助.如果有帮助，请投票.
谢谢

这里有四种方法可以做到这一点:

http://博客. msdn.com/b/amitjet/archive/2009/12/11/sql-server-comma-separated-string-to-table.aspx [ set ANSI_NULLS ON 设置 QUOTED_IDENTIFIER >打开 转到 创建 功能 [dbo].[fn_Splitter]( @ ID Varchar ( 100 ))) 返回 @ Tbl_IDs 表(ID Int ) As 开始 - 附加逗号 设置 @ IDs = @ IDs + ' ，' - 保持搜索位置的索引 声明 @ Pos1 Int 声明 @ pos2 Int - 从第一个字符开始 设置 @ Pos1 = 1 设置 @ Pos2 = 1 而 @ Pos1< len( @ ids ) 开始 设置 @ Pos1 = CharIndex(' ，'， @ IDs ， @ Pos1 ) 插入 @ Tbl_IDs 选择 Cast(子字符串( @ IDs ， @ Pos2 ，@ Pos1- @ Pos2) Int ) - 转到下一个非逗号字符 设置 @ Pos2 = @ Pos1 + 1 - 从下一个字符开始搜索 设置 @ Pos1 = @ Pos1 + 1 结束 返回 结束

现在，您的存储过程应该是这样的

  ALTER  功能 [dbo].[Check_MemberIDCombination]
 (
   @ Ids   VARCHAR ( 1000 )
  )
 返回  VARCHAR ( 20 )
 AS 
开始
 
 DECLARE   @ Msg   VARCHAR ( 20 )；
声明  @ count   int 
 
 选择 @Ids =(选择 转换( varchar ，Id)+ ' ，' 来自((<选择 ID 来自 dbo.Fn_Splitter( @ Ids ))) as  tmp 通过 ID   xml路径('  '))
选择 @ Ids =子字符串( @ Ids ， 1 ，Len( @ Ids ))


声明  @ Computing  表(id  nvarchar ( 1000 )，Member_Id  nvarchar ( 1000 ))
插入 插入  @ computing 
选择 ID，子字符串(Applicant_Member_Ids， 1 ，len(Applicant_Member_Ids)-1)选择 ID，
    (选择 转换( varchar ，Applicant_Member_Id) + ' ，'  as  [文本()] 来自 Savings_Account_Applicant 其中 ID = saa .ID 订单 通过 Applicant_Member_Id   xml路径(' ')) as  Applicant_Member_Ids
    来自 Savings_Account_Applicant saa
    组 通过 ID
) as  Savings_Account  ORDER   BY  ID
 
设置  @ count  =( select  count( *)来自  @ Computing  其中 Member_Id = @Ids)

如果 @ count> 0
开始

  SET   @ Msg  = ' 已经存在
结束
 ELSE 
 开始
  SET   @ Msg  = ' 可用'
 
 END 
返回  @ Msg 
结束 

祝您编码愉快！
:)

hi,

how to insert string data into a table variable

ex: string= ''41,21,23,25''

i want to insert the above ID into a table variable as below

ID
41
21
23
25

解决方案

CREATE FUNCTION DBO.FUN_SPLIT
(
	@DATA VARCHAR(2000),	
	@SEP VARCHAR(5)
)  
RETURNS @TEMP TABLE (ID INT IDENTITY(1,1), DATA NVARCHAR(100)) 
AS  
/************************************************************************************** TESTING			:   SELECT * FROM DBO.FUN_SPLIT('A,B,C',',')
**************************************************************************************/
BEGIN 	
	DECLARE @CNT INT	
	SET @CNT = 1	
	WHILE (CHARINDEX(@SEP,@DATA)>0)
	BEGIN		
		INSERT INTO @TEMP (DATA)		
		SELECT	DATA = LTRIM(RTRIM(SUBSTRING(@DATA,1,CHARINDEX(@SEP,@DATA)-1)))		
		SET @DATA = SUBSTRING(@DATA,CHARINDEX(@SEP,@DATA)+1,LEN(@DATA))		
		SET @CNT = @CNT + 1	
	END		
	INSERT INTO @TEMP (DATA)	
	SELECT DATA = LTRIM(RTRIM(@DATA))	
	RETURN

END



I hope this will help u. If helped Vote it.
Thanks

here are four approaches to do the same:

http://blogs.msdn.com/b/amitjet/archive/2009/12/11/sql-server-comma-separated-string-to-table.aspx[^]

Create this function in your database

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE Function [dbo].[fn_Splitter] (@IDs Varchar(100) )  
Returns @Tbl_IDs Table  (ID Int)  As  
Begin 
 -- Append comma
 Set @IDs =  @IDs + ',' 
 -- Indexes to keep the position of searching
 Declare @Pos1 Int
 Declare @pos2 Int
  -- Start from first character 
 Set @Pos1=1
 Set @Pos2=1
 While @Pos1<len(@ids)
 Begin
  Set @Pos1 = CharIndex(',',@IDs,@Pos1)
  Insert @Tbl_IDs Select  Cast(Substring(@IDs,@Pos2,@Pos1-@Pos2) As Int)
  -- Go to next non comma character
  Set @Pos2=@Pos1+1
  -- Search from the next charcater
  Set @Pos1 = @Pos1+1
 End 
 Return
End

Now Your Store procedure should be like this

ALTER FUNCTION [dbo].[Check_MemberIDCombination]
 (
  @Ids VARCHAR(1000)
  ) 
 RETURNS VARCHAR(20)
AS 
BEGIN
 
DECLARE @Msg VARCHAR(20);
declare @count int
 
select @Ids=(select convert(varchar,Id) + ',' from ((select Id from dbo.Fn_Splitter(@Ids))) as tmp order by Id for xml path (''))
select @Ids= substring(@Ids,1,Len(@Ids))


DECLARE @Computed table(id nvarchar(1000),Member_Id nvarchar(1000))
insert into @computed
select ID, substring(Applicant_Member_Ids,1,len(Applicant_Member_Ids)-1) from
(
    select ID ,
    (select convert(varchar, Applicant_Member_Id) + ',' as [text()] from Savings_Account_Applicant where ID =saa.ID order by Applicant_Member_Id for xml path('')) as Applicant_Member_Ids
    from Savings_Account_Applicant saa
    group by ID
) as Savings_Account ORDER BY ID
 
set @count = ( select count(*) from @Computed where Member_Id=@Ids)

if @count>0
begin

 SET @Msg = 'Already Exists'
end 
ELSE
 begin
 SET @Msg = 'Available'
 
END
RETURN @Msg 
end

Happy Coding!
:)

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