Update and Insert in SQL

Question

0.00/5 (No votes)

See more:

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:

SQL

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:

SQL

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 5:28am

wonder-FOOL

Updated 22-Mar-13 6:01am

v3

Add a Solution

Comments

joshrduncan2012 22-Mar-13 11:31am

Where is your code with what you have attempted so far?

wonder-FOOL 22-Mar-13 12:01pm

please see my updates.

Maciej Los 22-Mar-13 12:02pm

How do you retrieve data from database into tblTemp?

phil.o 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 ^^

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)

Maciej Los · Answer 1 · 2013-03-22T06:26:00

Have a look at this example:

SQL

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

Corporal Agarn · Answer 2 · 2013-03-22T07:43:00

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;

Posted 22-Mar-13 7:43am

Corporal Agarn