在 while 循环中使用临时表中的行数 SQL Server 2008 [英] Using row count from a temporary table in a while loop SQL Server 2008

问题描述

我正在尝试在 SQL Server 2008 中创建一个过程，用于将临时表中的数据插入到现有表中.我想我已经很清楚了，我只是有一个循环问题.我需要临时表中的行数来确定循环何时完成.

I'm trying to create a procedure in SQL Server 2008 that inserts data from a temp table into an already existing table. I think I've pretty much figured it out, I'm just having an issue with a loop. I need the row count from the temp table to determine when the loop should finish.

我尝试以两种不同的方式使用@@ROWCOUNT；在 WHILE 语句中单独使用它，并在第一个循环完成时创建一个变量来尝试保存该值(参见下面的代码).

I've tried using @@ROWCOUNT in two different ways; using it by itself in the WHILE statement, and creating a variable to try and hold the value when the first loop has finished (see code below).

这些方法都没有奏效，我现在不知道该怎么做.在这种情况下是否可以使用@@ROWCOUNT，或者有其他更好的方法吗?

Neither of these methods have worked, and I'm now at a loss as to what to do. Is it possible to use @@ROWCOUNT in this situation, or is there another method that would work better?

CREATE PROCEDURE InsertData(@KeywordList varchar(max))
AS
BEGIN

--create temp table to hold words and weights
CREATE TABLE #tempKeywords(ID int NOT NULL, keyword varchar(10) NOT NULL); 

DECLARE @K varchar(10), @Num int, @ID int

SET @KeywordList= LTRIM(RTRIM(@KeywordList))+ ','
SET @Num = CHARINDEX(',', @KeywordList, 1)
SET @ID = 0

--Parse varchar and split IDs by comma into temp table
IF REPLACE(@KeywordList, ',', '') <> ''
BEGIN
    WHILE @Num > 0
    BEGIN
        SET @K= LTRIM(RTRIM(LEFT(@KeywordList, @Num - 1)))
        SET @ID = @ID + 1
        IF @K <> ''
        BEGIN
            INSERT INTO #tempKeywords VALUES (@ID, @K) 
        END
        SET @KeywordList = RIGHT(@KeywordList, LEN(@KeywordList) - @Num)
        SET @Num = CHARINDEX(',', @KeywordList, 1)
        --rowcount of temp table
        SET @rowcount = @@ROWCOUNT
    END
END

--declaring variables for loop
DECLARE @count INT
DECLARE @t_name varchar(30)
DECLARE @key varchar(30)
DECLARE @key_weight DECIMAL(18,2)
--setting count to start from first keyword
SET @count = 2
--setting the topic name as the first row in temp table
SET @t_name = (Select keyword from #tempKeywords where ID = 1)
--loop to insert data from temp table into Keyword table
WHILE(@count < @rowcount)
    BEGIN
        SET @key = (SELECT keyword FROM #tempKeywords where ID = @count)
        SET @key_weight = (SELECT keyword FROM #tempKeywords where ID = @count+2)
        INSERT INTO Keyword(Topic_Name,Keyword,K_Weight)
        VALUES(@t_name,@key,@key_weight)
        SET @count= @count +2
    END 
--End stored procedure  
END

推荐答案

解决第二部分问题:

INSERT INTO Keyword(Topic_Name,Keyword,K_Weight)
SELECT tk1.keyword, tk2.keyword, tk3.keyword
FROM
    #tempKeywords tk1
        cross join
    #tempKeywords tk2
        inner join
    #tempKeywords tk3
        on
           tk2.ID = tk3.ID - 1
WHERE
    tk1.ID = 1 AND
    tk2.ID % 2 = 0

(此代码应从 --declaring variables for loop 注释开始替换当前脚本中的所有内容)

(This code should replace everything in your current script from the --declaring variables for loop comment onwards)

这篇关于在 while 循环中使用临时表中的行数 SQL Server 2008的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！