how to insert string data into a temp table rowwise

Question

4.00/5 (1 vote)

See more:

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

Posted 8-Nov-12 18:29pm

sk. maqdoom ali

Add a Solution

6 solutions

Solution 2

SQL

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

Posted 8-Nov-12 18:38pm

kankeyan

Solution 1

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[^]

Posted 8-Nov-12 18:36pm

Om Prakash Pant

Solution 3

Create this function in your database

SQL

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

SQL

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!
:)

Posted 8-Nov-12 18:56pm

Aarti Meswania

Updated 8-Nov-12 19:21pm

v2

Comments

sk. maqdoom ali 9-Nov-12 1:19am

For the execution of dbo.[fn_Splitter]

it shows an error for the below line

While @Pos1<len(@ids)>

Msg 102, Level 15, State 1, Procedure fn_Splitter, Line 12
Incorrect syntax near '>'.

sk. maqdoom ali 9-Nov-12 1:21am

its just

While @Pos1

Aarti Meswania 9-Nov-12 1:23am

see updated solution while copy paste data it includes some symbols and tags it self

sk. maqdoom ali 9-Nov-12 3:51am

using this [dbo].[fn_Splitter] function shall we split the data into ascending order as input??
ex:24,35,78,45
into
24,35,45,78
to give input to [dbo].[Check_MemberIDCombination]

sk. maqdoom ali 9-Nov-12 4:11am

my requirement is
i have to convert string data values which passed as input into ascending order
and
the data which is return from the following query
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

and compare both of this if any combination is existed then 'Already Exists' else 'Available'

sk. maqdoom ali 9-Nov-12 4:18am

madam shall i ask one qsn??

sk. maqdoom ali 9-Nov-12 4:19am

how much experience do u have in db??

Aarti Meswania 9-Nov-12 5:15am

2.5 yrs
and you are posing question many time but each time you have some different requirement
where do you stuck?

what you exactly want?
you had said earlier that you want that Applicant should not be include in more than one joined account which is quite simple just count(*)
and now,
you are searching that applicant combination should not be repeat?

tell us your exact requirement so, that this issue will be solved easily
:)

sk. maqdoom ali 9-Nov-12 5:20am

I am sorry for that actually the problem is with my LEAD.like this for every time he is asking me to do in different way without giving me proper requirement.

Aarti Meswania 9-Nov-12 5:27am

just ask him
one Applicant can have multiple joined accounts?
if he say no
then just fire query
select case when count(*)>0 then 'Exist' else 'Available' end as Response from Savings_Account_Applicant where Applicant_Member_Id = ...

sk. maqdoom ali 9-Nov-12 5:41am

what you send queries before are very useful to me from those two as i am mentioning below

Alter 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

sk. maqdoom ali 9-Nov-12 6:23am

hello

Aarti Meswania 9-Nov-12 6:28am

first is A function
second is Store procedure that you have create, I have just include lines which are highlighted by underline. (in that underlined portion I have use function 'fn_Splitter'

you have problem @Ids is not in ascending order so that Underlined lines will do whole process to make @Ids in asc order,

now use it and tell me if any doubt okay? :)

sk. maqdoom ali 9-Nov-12 6:31am

i am writing the whole in Add your solution here
please check it,

Aarti Meswania 9-Nov-12 6:36am

what you mean to say?
"i am writing the whole in Add your solution here ... please check it"???

sk. maqdoom ali 9-Nov-12 5:45am

i think i am clear??

Solution 4

Create below function

SQL

CREATE FUNCTION [dbo].[String_Tokenizer]
(
	@RowData nvarchar(max),
	@SplitOn nvarchar(5)
)  
RETURNS @RtnValue table 
(
	Data nvarchar(100)
) 
AS  
BEGIN 
	Declare @Cnt int
	Set @Cnt = 1
 
	While (Charindex(@SplitOn,@RowData)>0)
	Begin
		Insert Into @RtnValue (data)
		Select 
			Data = ltrim(rtrim(Substring(@RowData,1,Charindex(@SplitOn,@RowData)-1)))
 
		Set @RowData = Substring(@RowData,Charindex(@SplitOn,@RowData)+1,len(@RowData))
		Set @Cnt = @Cnt + 1
	End
	
	Insert Into @RtnValue (data)
	Select Data = ltrim(rtrim(@RowData))
 
	Return
END

The above function will return a temporary table.

Sample code:

SQL

select * from [dbo].[String_Tokenizer]('1,2,3,4',',')

Output:

Data
1
2
3
4

Posted 8-Nov-12 18:56pm

Shanalal Kasim

Solution 6

Check this out

http://stackoverflow.com/questions/3265316/how-to-split-and-insert-csv-data-into-a-new-table-in-single-statement[^]

Posted 9-Nov-12 0:49am

deepakdynamite

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

sk. maqdoom ali · Accepted Answer · 2012-11-09T00:35:00

Solution 5

SQL

ALTER 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

SQL

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 asc for xml path (''))
select @Ids= (select substring(@Ids,1,Len(@Ids)))

--DECLARE @Computed table(id nvarchar(1000),Member_Id nvarchar(1000))
--insert into @computed
set @count = (select count(*) from
(
select ID, substring(Applicant_Member_Ids,1,len(Applicant_Member_Ids)-1) strings from
(
    select ID ,
    (select convert(varchar, Distinct 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 ) tab1
where tab1.strings=@Ids)
 
 --= ( 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

now i am executing it by as below input

select dbo.Check_MemberIDCombination('41,51') as Msg

the above '150,150,150,150' is already existed in the table

but here i am getting the Msg as 'Available' instead of 'Already Exists'

Posted 9-Nov-12 0:35am

sk. maqdoom ali

Updated 9-Nov-12 0:55am

v2

Comments

Aarti Meswania 9-Nov-12 6:39am

what is it '150,150,150,150' ?

please give inputs
some rows of computed Query

sk. maqdoom ali 9-Nov-12 6:45am

id strings
18 39,39,39,39,51
102 41,51
113 41,116
155 39,217
793 150,150,150,150

sk. maqdoom ali 9-Nov-12 6:47am

select dbo.Check_MemberIDCombination('41,51') as Msg

the above '41,51' is already existed in the table

but here i am getting the Msg as 'Available' instead of 'Already Exists'

Aarti Meswania 9-Nov-12 6:57am

see updated solution first.

it is not good 4 times it shows 150 in last line and 39 in first line

Aarti Meswania 9-Nov-12 7:00am

change this line
select @Ids= substring(@Ids,1,Len(@Ids))

with
select @Ids= substring(@Ids,1,Len(@Ids) -1 )

you will get result

Aarti Meswania 9-Nov-12 7:13am

Did it worked or not???

sk. maqdoom ali 9-Nov-12 7:22am

its working fine just now i explained this to my LEAD

thanks a lot madam
u helped me a lot:
thank u

sk. maqdoom ali 9-Nov-12 7:25am

in which organization u r working??

Aarti Meswania 9-Nov-12 7:26am

okay finally it been solved
now, mark it as answer so,
other persons stop wasting time on this problem It is good habit.

sk. maqdoom ali 9-Nov-12 8:09am

madam

in which organization u r working??

Aarti Meswania 9-Nov-12 8:15am

I am windows application developer in ahmedabad gujarat

sk. maqdoom ali 9-Nov-12 8:17am

i am sql developer , Hyderabad

Aarti Meswania 9-Nov-12 8:19am

okay how much experience do you have?
and in which technology you work ASP.net or windows application in .net?

sk. maqdoom ali 9-Nov-12 8:21am

i have 8 months experience

i joined as a DBA
i will work on database only

Aarti Meswania 9-Nov-12 8:25am

good best of luck

sk. maqdoom ali 9-Nov-12 8:28am

thank u,,

happy weekend

Aarti Meswania 9-Nov-12 8:32am

welcome :)
Happy weekends

sk. maqdoom ali 9-Nov-12 8:34am

hmm....

sk. maqdoom ali 11-Nov-12 23:45pm

hi , gd mrng

sk. maqdoom ali 14-Nov-12 4:21am

hi,
shall we declare
output return value in the returns clause in functions??

sk. maqdoom ali 14-Nov-12 5:36am

busy??