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 : 16886 | Status : [Member] | Replies : 5
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



Responses

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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Login to post response