使用存储过程循环遍历表

The aim of the stored procedure below is to populate the location tables from another location tables. I discovered when the procedure is executed the records has a lot of duplicates. I have gone through the code several times. Cannot find anything wrong with it.

下面的存储过程的目的是从其他位置表填充位置表。我发现在执行该过程时，记录有很多重复。我已经多次查看了代码。找不到任何问题。

Stored procedure:

存储过程：

ALTER PROCEDURE [dbo].[sp_Populate_Location]
   @school_id int
AS
    DECLARE @country_id int, @country_id_new int, @country_name nvarchar(50)
    DECLARE @state_id int, @state_id_new int, @state_name nvarchar(50)
    DECLARE @town_city_id int, @town_city_name nvarchar(50)
    DECLARE @local_government_id int, @local_government_name nvarchar(50)

    DECLARE @country TABLE  (
                         idx smallint Primary Key IDENTITY(1,1),
                         country_id int, 
                         country_name nvarchar(50)
                     )

    DECLARE @state TABLE (
                        idx smallint Primary Key IDENTITY(1,1),
                        state_id int, 
                        country_id int, 
                        state_name nvarchar(50)
                    )

    DECLARE @town_city TABLE (
                        idx smallint Primary Key IDENTITY(1,1),
                        town_city_name nvarchar(50)
                    )

    DECLARE @local_government TABLE (
                        idx smallint Primary Key IDENTITY(1,1),
                        local_government_name nvarchar(50)
                    )

    -- populate country table
    INSERT @country
        SELECT CountryId, CountryName 
        FROM tbl_Country 
        WHERE IsActive = 1

    -- enumerate the table
    DECLARE @icount_1 int
    DECLARE @numrows_country int

    SET @icount_1 = 1
    SET @numrows_country = (SELECT COUNT(*) FROM @country)

    IF @numrows_country > 0

    WHILE (@icount_1 <= (SELECT MAX(idx) FROM @country))
    BEGIN
        -- get the next country primary key
        SET @country_id = (SELECT country_id FROM @country WHERE idx = @icount_1)
        SET @country_name = (SELECT country_name FROM @country WHERE idx = @icount_1)

        INSERT tbl_SchoolCountry(CountryName, IsActive, CreatedBy, CreatedOn, SchoolId) 
        VALUES(@country_name, 1, 1, GETDATE(), @school_id);

        SET @country_id_new = (SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY])

        INSERT @state
            SELECT StateId, CountryId, StateName 
            FROM tbl_State 
            WHERE CountryId = @country_id

        DECLARE @icount_2 int
        DECLARE @numrows_state int

        SET @icount_2 = 1
        SET @numrows_state = (SELECT COUNT(*) FROM @state)

        IF @numrows_state > 0
        WHILE (@icount_2 <= (SELECT MAX(idx) FROM @state))
        BEGIN

            SET @state_id = (SELECT state_id FROM @state WHERE idx = @icount_2)
            SET @state_name = (SELECT state_name FROM @state WHERE idx = @icount_2)

            INSERT tbl_SchoolState(CountryId, StateName, IsActive, CreatedBy, CreatedOn, SchoolId) 
            VALUES(@country_id_new, @state_name, 1, 1, GETDATE(), @school_id);

            SET @state_id_new = (SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY])

            -- Town and City
            INSERT @town_city
                SELECT TownCityName 
                FROM tbl_TownCity 
                WHERE StateId = @state_id AND IsActive = 1

            DECLARE @icount_3 int
            DECLARE @numrows_town_city int

            SET @icount_3 = 1
            SET @numrows_town_city = (SELECT COUNT(*) FROM @town_city)

            IF @numrows_town_city > 0
            WHILE (@icount_3 <= (SELECT MAX(idx) FROM @town_city))
            BEGIN
                SET @town_city_name = (SELECT town_city_name FROM @town_city WHERE idx = @icount_3)

                INSERT tbl_SchoolTownCity(CountryId, StateId, TownCityName, IsActive, CreatedBy, CreatedOn, SchoolId) 
                VALUES(@country_id_new, @state_id_new, @town_city_name, 1, 1, GETDATE(), @school_id);

                SET @icount_3 = @icount_3 + 1
            END

    -- LGA

    INSERT @local_government
    SELECT LocalGovernmentName FROM tbl_LocalGovernment where StateId = @state_id and IsActive = 1

    DECLARE @icount_4 int
    DECLARE @numrows_lga int

    SET @icount_4 = 1
    SET @numrows_lga = (SELECT COUNT(*) FROM @local_government)
    IF @numrows_lga > 0
    WHILE (@icount_4 <= (SELECT MAX(idx) FROM @local_government))
    BEGIN

    SET @local_government_name = (SELECT local_government_name FROM @local_government WHERE idx = @icount_4)

    INSERT tbl_SchoolLocalGovernment(CountryId, StateId, LocalGovernmentName, IsActive, CreatedBy, CreatedOn, SchoolId) 
    VALUES(@country_id_new, @state_id_new, @local_government_name,1,1,GETDATE(),@school_id);

    SET @icount_4 = @icount_4 + 1
    END

    SET @icount_2 = @icount_2 + 1
    END
        -- increment counter for next country
        SET @icount_1 = @icount_1 + 1
    END

1 个解决方案

#1

I discovered what the problem was. The temp tables were meant to be declared before inserting into them.

我发现了问题所在。临时表是在插入之前声明的。

Before:

之前：

 DECLARE @country TABLE  (
                         idx smallint Primary Key IDENTITY(1,1),
                         country_id int, 
                         country_name nvarchar(50)
                     )

    DECLARE @state TABLE (
                        idx smallint Primary Key IDENTITY(1,1),
                        state_id int, 
                        country_id int, 
                        state_name nvarchar(50)
                    )

    DECLARE @town_city TABLE (
                        idx smallint Primary Key IDENTITY(1,1),
                        town_city_name nvarchar(50)
                    )

    DECLARE @local_government TABLE (
                        idx smallint Primary Key IDENTITY(1,1),
                        local_government_name nvarchar(50)
                    )

After:

后：

DECLARE @country TABLE (
    idx smallint Primary Key IDENTITY(1,1)
    , country_id int, country_name nvarchar(50)
    )

    -- populate country table
    INSERT @country
    SELECT CountryId, CountryName FROM tbl_Country where IsActive = 1
.
.
.
.
.
.
.
    DECLARE @state TABLE (
    idx smallint Primary Key IDENTITY(1,1)
    , state_id int, country_id int, state_name nvarchar(50)
    )

    INSERT @state
    SELECT StateId, CountryId, StateName FROM tbl_State where CountryId = @country_id
.
.
.
.
.
.
    DECLARE @town_city TABLE (
    idx smallint Primary Key IDENTITY(1,1)
    , town_city_name nvarchar(50)
    )

    INSERT @town_city
    SELECT TownCityName FROM tbl_TownCity where StateId = @state_id and IsActive = 1
.
.
.
.
.
.
    DECLARE @local_government TABLE (
    idx smallint Primary Key IDENTITY(1,1)
    , local_government_name nvarchar(50)
    )
    INSERT @local_government
    SELECT LocalGovernmentName FROM tbl_LocalGovernment where StateId = @state_id and IsActive = 1

By doing so, it clears the temp table before new loop.

通过这样做，它在新循环之前清除临时表。

#1