在SQL中更新和插入 [英] Update and Insert in SQL

问题描述

大家好，



我从表中检索了一些数据并将它们插入临时表中，然后让我们说 tblTemp 。

此表的输出为：

siteid p_id desc 代码

11 1测试1 22

11 1测试2 22

11 1测试3 22

11 1测试4 22



我试图完成递增该临时表的siteid并插入表。我怎么能这样做？

最终的结果应该是：

siteid p_id desc 代码

11 1测试1 22

12 1测试2 22

13 1测试3 22

14 1测试4 22



我正在考虑循环遍历结果集并插入它们。但有更好的方法吗？有人能引导我如何解决它？谢谢



我试过：

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 

我没有增加网站ID。

I failed to increment the site id.

推荐答案

看一下这个例子：

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

可能就像在临时表中添加另一列一样

未经测试。

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;

这篇关于在SQL中更新和插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！