How to insert values in a Temp table from SP SQL SERVER 2005

Posted by Self-Innovator under Sql Server on 12/20/2012 | Points: 10 | Views : 17861 | Status : [Member] | Replies : 5

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Hi all,
When i ran this query i get result as 2 and 5 and two individual rows....

DECLARE @Test VARCHAR(1000), @RecSet VARCHAR(500), @ID VARCHAR(10), @Value VARCHAR(60)

DECLARE @StartingPos INT,@RecPos INT

DECLARE @TEMPEXPECTATION TABLE(COMPLEXIONID INT)

SET @StartingPos= 1

SET @Test =(select cmp.complexion1 from customerexpectation cmp where cmp.customerid='be65cb50-cc1d-47a5-a3a2-657fae4e6519')

WHILE @StartingPos<=LEN(@Test)

BEGIN

    SELECT @RecPos = CHARINDEX(',',@Test,@StartingPos)

    SELECT @RecSet=SUBSTRING(@Test,@StartingPos,@RecPos-@StartingPos)
    
	PRINT @RecSet
	
	--INSERT INTO @TEMPEXPECTATION(@ID)


	SELECT @StartingPos=@RecPos+1

END

Now i need to insert this values 2 and 5 in a temporary table using the above query to made as Stored Procedure...Pls find the solution...

Join Hands Change lives
Thanks & Regards
Straight Edge Society

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Pandians on: 12/21/2012 [Member] [MVP] Silver | Points: 25

0	Do you want to INSERT the result of "@RecSet" into a Temp table directly using the script ? (or) Do you want to create a Procedure and then INSERT the result into a Temp table ? Cheers www.SQLServerbuddy.blogspot.com iLink Multitech Solutions Self-Innovator, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Self-Innovator on: 12/21/2012 [Member] Bronze | Points: 25

0	Hi pandianS.. I wanted to make use of stored proc to insert the result of "@RecSet" values in to a temp table.. Join Hands Change lives Thanks & Regards Straight Edge Society Self-Innovator, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Pavanandey on: 12/21/2012 [Member] Bronze | Points: 25

0	DECLARE @FirstTable TABLE (RandomInteger INT) //Creating new temp table INSERT INTO @FirstTable VALUES (10000) // insert value in to temp table Mark the answer if this helps you Thanks Pavan Kumar Mark Answer if this fits the need Self-Innovator, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Self-Innovator on: 12/21/2012 [Member] Bronze | Points: 25

0	I have written this sp..please have a look.. CREATE PROC sp_SplitVal @SplitValues nvarchar(max) AS DECLARE @RecSet VARCHAR(500), @ID VARCHAR(10), @Value VARCHAR(60) DECLARE @StartingPos INT,@RecPos INT DECLARE @TEMPTABLE TABLE(SPLITVAL INT) SET @StartingPos= 1 SET @SplitValues =(select cmp.complexion1 from customerexpectation cmp where cmp.customerid='68572b93-16bd-4764-b75f-3aad35fef04b') WHILE @StartingPos<=LEN(@SplitValues) BEGIN SELECT @RecPos = CHARINDEX(',',@SplitValues,@StartingPos) SELECT @RecSet=SUBSTRING(@SplitValues,@StartingPos,@RecPos-@StartingPos) --PRINT @RecSet INSERT INTO TEMPTABLE VALUES(@RecSet) SELECT @StartingPos=@RecPos+1 END Is i am wrong somewhere ...i haven't used like these before in my sp's please guide it..now how can i check the values of my temptable.. *when i try running select from @Temptable statement it throws error invalid object name @TEMPTABLE** Join Hands Change lives Thanks & Regards Straight Edge Society Self-Innovator, if this helps please login to Mark As Answer. \| Alert Moderator

I have written this sp..please have a look..

CREATE PROC sp_SplitVal

@SplitValues nvarchar(max)

AS

DECLARE @RecSet VARCHAR(500), @ID VARCHAR(10), @Value VARCHAR(60)



DECLARE @StartingPos INT,@RecPos INT



DECLARE @TEMPTABLE TABLE(SPLITVAL INT)



SET @StartingPos= 1



SET @SplitValues =(select cmp.complexion1 from customerexpectation cmp where cmp.customerid='68572b93-16bd-4764-b75f-3aad35fef04b')



WHILE @StartingPos<=LEN(@SplitValues)



BEGIN



    SELECT @RecPos = CHARINDEX(',',@SplitValues,@StartingPos)



    SELECT @RecSet=SUBSTRING(@SplitValues,@StartingPos,@RecPos-@StartingPos)

    	

	--PRINT @RecSet



	INSERT INTO TEMPTABLE VALUES(@RecSet)



	SELECT @StartingPos=@RecPos+1

END

Is i am wrong somewhere ...i haven't used like these before in my sp's please guide it..now how can i check the values of my temptable..
when i try running select * from @Temptable statement it throws error invalid object name @TEMPTABLE

Join Hands Change lives
Thanks & Regards
Straight Edge Society

Self-Innovator, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Pavanandey on: 12/21/2012 [Member] Bronze | Points: 25

0	Create Proc Temp_Example as begin DECLARE @FirstTable TABLE (RandomInteger INT) INSERT INTO @FirstTable VALUES (200) INSERT INTO @FirstTable VALUES (400) INSERT INTO @FirstTable VALUES (600) Select * from @FirstTable End exec Temp_Example Output RandomInteger ------------------------- 200 400 600 Alter Proc Temp_Example as DECLARE @FirstTable TABLE (RandomInteger INT) begin DECLARE @intFlag INT SET @intFlag = 1 WHILE (@intFlag <=5) BEGIN INSERT INTO @FirstTable VALUES (@intFlag) SET @intFlag = @intFlag + 1 END Select * from @FirstTable End exec Temp_Example RandomInteger ------------------------- 1 2 3 4 5 Make the necessay changes Thanks Pavan Kumar Mark Answer if this fits the need Self-Innovator, if this helps please login to Mark As Answer. \| Alert Moderator

Create Proc Temp_Example
as
begin
DECLARE @FirstTable TABLE (RandomInteger INT)
INSERT INTO @FirstTable VALUES (200)
INSERT INTO @FirstTable VALUES (400)
INSERT INTO @FirstTable VALUES (600)
Select * from @FirstTable
End

exec Temp_Example

Output
RandomInteger
-------------------------
200
400
600

Alter Proc Temp_Example
as
DECLARE @FirstTable TABLE (RandomInteger INT)
begin

DECLARE @intFlag INT
SET @intFlag = 1
WHILE (@intFlag <=5)
BEGIN
INSERT INTO @FirstTable VALUES (@intFlag)
SET @intFlag = @intFlag + 1
END
Select * from @FirstTable
End

exec Temp_Example
RandomInteger
-------------------------
1
2
3
4
5

Make the necessay changes

Thanks
Pavan Kumar
Mark Answer if this fits the need

Self-Innovator, if this helps please login to Mark As Answer. | Alert Moderator

Latest Posts