Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-server-2005
Hi All,
 
In my scenario i have a title column and i want to select each word in title and insert seperately into another table.
For example:
Table name is Book
Id Tilte
1 One day in my life
 
second table name is Master
id title
1 one
2 day
3 in
4 my
5 life
like these i want to insert into master table.
Posted 8-Jan-13 22:47pm
josh-jw10.8K

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

You will probably want this as a stored procedure with @INSTR as a parameter, but it should work:
DECLARE @INSTR as VARCHAR(MAX)
SET @INSTR = 'One day in my life '
DECLARE @SEPERATOR as VARCHAR(1)
DECLARE @SP INT
DECLARE @VALUE VARCHAR(1000)
SET @SEPERATOR = ' '
WHILE PATINDEX('%' + @SEPERATOR + '%', @INSTR ) <> 0
BEGIN
   SELECT  @SP = PATINDEX('%' + @SEPERATOR + '%',@INSTR)
   SELECT  @VALUE = LEFT(@INSTR , @SP - 1)
   SELECT  @INSTR = STUFF(@INSTR, 1, @SP, '')
   INSERT INTO MyTable (Title) VALUES (@VALUE)
END
  Permalink  
Comments
josh-jw at 9-Jan-13 4:27am
   
Thanks,Griff.
Is it possible that my table containing 10 tiltes.within one select i can pass all titles into this approach and do the above operation?
OriginalGriff at 9-Jan-13 4:32am
   
Sorry? I don't understand what you are asking.
Would you mind trying to ask that in a different way, perhaps with an example?
josh-jw at 9-Jan-13 5:45am
   
i would like to say another scenario.
is it possible that i call one stored procedure inside a function and after that i call that function in query like
select title,fn(title) from book.?
OriginalGriff at 9-Jan-13 5:54am
   
Why would you want to do that? Every time you tried to retrieve info on the title "One day in my life" is would add 5 rows to a table, which already should contain them.
I would create an SP which did both inserts: the main title, and each word and use that instead of trying to insert the title.
What are you trying to achieve with this anyway? There may be a better way to do it.
josh-jw at 9-Jan-13 5:58am
   
i want to achieve the scenario like my book table containing 10 title and i want to insert that title like each word as one record into master table.i want to do
selecting and inserting at a time,like bulk insertion.
OriginalGriff at 9-Jan-13 6:05am
   
I understand that, but why? What does it give you? How does it help you? What are you trying to do with the individual words?
josh-jw at 9-Jan-13 6:08am
   
actually current task is that one .i know that will not help.but i have to do like that only. if do the scenario with i explained above i am getting error like
"Only functions and extended stored procedures can be executed from within a function."
josh-jw at 9-Jan-13 6:08am
   
is there any other way to do this scenario?
OriginalGriff at 9-Jan-13 6:11am
   
Strange thing to do, but...
Just make it a function instead of a stored procedure.
http://msdn.microsoft.com/en-us/library/ms186755(v=sql.105).aspx
josh-jw at 9-Jan-13 6:26am
   
BUt we cant do insertion inside function.
OriginalGriff at 9-Jan-13 6:38am
   
Oops! I forgot that! You can't use a function then...
You wiull have to try an extended stroed procedure - but I have never needed one, so you are venturing into uncharted waters for me!
http://support.microsoft.com/kb/190987
might help.
josh-jw at 9-Jan-13 6:48am
   
didnt get any idea .i think i cant do bulk insertion.loop concept have to implement here.
OriginalGriff at 9-Jan-13 7:18am
   
Why are you trying to do it anyway? Why store each word in a separate row?
josh-jw at 9-Jan-13 7:30am
   
for filtering in user interface. how we can use while loop in this scenario? is it possible to select one row from result set and send title to stored procedure in while loop?
OriginalGriff at 9-Jan-13 7:43am
   
You mean as in "find me all the titles that contain the word 'day'"?
Have you tried
SELECT * FROM MyTable WHERE Title LIKE '%day%'

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

  Print Answers RSS
0 OriginalGriff 520
1 Maciej Los 290
2 Richard MacCutchan 265
3 BillWoodruff 265
4 Suraj Sahoo | Coding Passion 155
0 OriginalGriff 8,764
1 Sergey Alexandrovich Kryukov 7,437
2 DamithSL 5,639
3 Maciej Los 5,279
4 Manas Bhardwaj 4,986


Advertise | Privacy | Mobile
Web03 | 2.8.1411028.1 | Last Updated 9 Jan 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100