Inserting values into Temp table SQL Server 2005 using SP

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

SQL

Hi all,
When i ran this query i get result as 2 and 5 and two individual rows....wher i have the values 2,5, in my column of my table...
[CODE]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
[/CODE]
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...

Posted 20-Dec-12 17:56pm

sahmed4

Add a Solution

Comments

arindamrudra 21-Dec-12 0:03am

With in that While loop you want to insert? What are the column names or variable names that you want to insert in the temporary table.

2 solutions

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)

arindamrudra · Answer 1 · 2012-12-20T18:35:00

Solution 2

SQL

Create TABLE #tempdata (ID INT IDENTITY(1,1), FirstValue INT, LastValue INT)
INSERT INTO #tempdata (FirstValue,LastValue) VALUES (@RecPos,@RecSet)

Posted 20-Dec-12 18:35pm

arindamrudra

Updated 20-Dec-12 19:37pm

v3

sahmed4 · Answer 2 · 2012-12-20T18:11:00

Solution 1

Hi..while run this query i am getting the values as 2,5, then by using some splitting mechanism i am splitting the values as 2 and 5 respectively now i need to insert these two values in a temp table with column as complexionid...i hope it's clear arindamurdra...

SQL

select cmp.complexion1 from customerexpectation cmp where cmp.customerid='be65cb50-cc1d-47a5-a3a2-657fae4e6519'

Posted 20-Dec-12 18:11pm

sahmed4

Comments

arindamrudra 21-Dec-12 0:31am

Your query will return only one value either 2 or 5. You are missing some thing.

sahmed4 21-Dec-12 0:38am

see this below updated query
DECLARE @Test VARCHAR(1000), @RecSet VARCHAR(500), @ID VARCHAR(10), @Value VARCHAR(60)

DECLARE @StartingPos INT,@RecPos INT

SET @StartingPos= 1

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

WHILE @StartingPos<=LEN(@Test)

BEGIN

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

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

SET @ID=@RecSet
PRINT @ID

-- SELECT CC.CUSTOMERID,CC.COMPLEXION1,CM.ID FROM CUSTOMEREXPECTATION CC
-- LEFT OUTER JOIN
-- COMPLEXION CM
-- ON CC.COMPLEXION1=@ID WHERE CUSTOMERID='68572b93-16bd-4764-b75f-3aad35fef04b'
--
SELECT @StartingPos=@RecPos+1

END

and the Variable @Test consists of values 2,5, and im getting result in Print @ID statemant as 2 and 5 in two indiviual rows..now i need to insert these values in temp table..pls find soln...i hope now my req is clear..

arindamrudra 21-Dec-12 1:38am

Please check my solution.