Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-Server T-SQL
Hello all,
 
I have a retrieved some data from a table and inserted them into a temporary table lets say tblTemp.
The output of this table is:
siteid p_id desc code
11 1 test 1 22
11 1 test 2 22
11 1 test 3 22
11 1 test 4 22
 
I am trying to accomplish increment the siteid of that temporary table and insert to a table. How can I do this?
The final outcome should be like:
siteid p_id desc code
11 1 test 1 22
12 1 test 2 22
13 1 test 3 22
14 1 test 4 22
 
I was thinking of a looping through the result set and insert them. But is there a better way? Can someone lead me how can i solve it? Thanks
 
I have tried:
DECLARE @curcount INT
SET @curcount = 0 
WHILE(@curcount<select count(code) from tblTemp)
BEGIN 
INSERT INTO tblACC (siteid, p_id, desc, code)
SET @curcount = @curcount + 1
END
 
Also tried:
DECLARE CUR CURSOR FAST_FORWARD READ_ONLY FOR
select * from tblTemp
DECLARE @varsiteId int
DECLARE @varpId int
DECLARE @vardesc varchar(100)
DECLARE @varcode int
 
OPEN CUR
FETCH NEXT FROM CUR INTO @varsiteId, @varpId, @vardesc, @varcode
WHILE @@FETCH_STATUS = 0
BEGIN 
UPDATE tblTemp
SET siteid = select max(siteid) + 1 from tblTemp where siteid = @varsiteId
FETCH NEXT FROM CUR INTO @varsiteId, @varpId, @vardesc, @varcode
END
CLOSE CUR
DEALLOCATE CUR 
I failed to increment the site id.
Posted 22-Mar-13 6:28am
Edited 22-Mar-13 7:01am
v3
Comments
joshrduncan2012 at 22-Mar-13 11:31am
   
Where is your code with what you have attempted so far?
wonder-FOOL at 22-Mar-13 12:01pm
   
please see my updates.
Maciej Los at 22-Mar-13 12:02pm
   
How do you retrieve data from database into tblTemp?
phil.o at 22-Mar-13 12:34pm
   
There is someting that annoys me : you are getting the siteid value from another table, right?
So, if you change it in your temporary table, how will you know to which original line it is refering?
But maybe you simply do not need to know that... Was just curious ^^
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Have a look at this example:
CREATE TABLE #tblACC (siteid INT, p_id INT, descr VARCHAR(30), code INT)
 
DECLARE @curcount INT
DECLARE @code INT
 
SET @curcount = 0
SET @code = 10
 
WHILE(@curcount<@code)
BEGIN
    INSERT INTO #tblACC (siteid, p_id, descr, code)
    VALUES(11, 1, 'test' + CONVERT(VARCHAR(30), @curcount), 22)
    SET @curcount = @curcount + 1
END
 
--SELECT *
--FROM #tblAcc

SET @curcount = 0
 
SELECT @code =COUNT(code) from #tblACC
WHILE(@curcount<@code)
BEGIN
    SET @curcount = @curcount + 1
    UPDATE #tblACC SET siteid = CONVERT(INT, 10 + @curcount) WHERE descr = 'test' + CONVERT(VARCHAR(10), @curcount)
END
 
SELECT *
FROM #tblAcc
 
DROP TABLE #tblACC
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

Maybe something like adding another column to the temp table
Not tested.
DECLARE @mid INT
SELECT @mid = max(siteid) FROM tblTEMP
ALTER TABLE tblTEMP
ADD inc INT (@mid, 1) NOT NULL;
  Permalink  

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

  Print Answers RSS
0 OriginalGriff 195
1 ProgramFOX 130
2 Maciej Los 105
3 Sergey Alexandrovich Kryukov 105
4 Afzaal Ahmad Zeeshan 82
0 OriginalGriff 6,564
1 Sergey Alexandrovich Kryukov 6,048
2 DamithSL 5,228
3 Manas Bhardwaj 4,717
4 Maciej Los 4,150


Advertise | Privacy | Mobile
Web01 | 2.8.1411022.1 | Last Updated 22 Mar 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