Microsoft SQL Server 2008 游标

发布于 2024-12-03 02:18:41 字数 10965 浏览 1 评论 0原文

我有一个 #temptable，其中包含正在使用游标读取的地址信息。光标效果很好。游标中的 if 语句对于 #table 中的第一条记录100%有效，但对于第二条记录它似乎总是返回 false。

if 语句的想法是检查数据库中地址表中是否存在该值，如果存在则获取 id。如果没有，则插入该行并获取 id。单独使用 if 语句可以 100% 工作。当我将它添加到光标中时，它就会中断。几乎就像变量被以某种方式覆盖一样。

因此，如果我的#table 中包含同一行，则该行仍然会被插入到address_table 中两次，而不是一次。第一次插入地址（如果不存在），第二次应该返回表中地址的 id

我已经取出了执行 convert() 的打印语句，但它没有'似乎有所作为。

这是独立的 if 语句。

DECLARE @COMP_NUM AS VARCHAR(100) = '4'
DECLARE @COMP_NAME AS VARCHAR(100) = 'TAGASTE'
DECLARE @STREET_NUM AS VARCHAR(100) = '150'
DECLARE @STREET_NAME AS VARCHAR(100) = 'WILLSON'
DECLARE @STREET_TYPE AS INT = 1
DECLARE @SUB AS VARCHAR(100) = 'FAIRLANDS'
DECLARE @CITY AS VARCHAR(100) = 'JOHANNESBURG'
DECLARE @HOMEPHONE AS VARCHAR(100) = '0112355566'
DECLARE @EXISTS AS INT
DECLARE @ADD_ID AS INT

SET @EXISTS = (SELECT COUNT(ID) FROM CARETEAMZ..ADDRESS_BOOK WHERE HOME_PHONE=@HOMEPHONE 
AND COMPLEX_NUMBER = @COMP_NUM
AND COMPLEX_NAME = @COMP_NAME
AND STREET_NUMBER = @STREET_NUM
AND STREET_NAME = @STREET_NAME
AND STREET_TYPE = @STREET_TYPE
AND SUBURB = @SUB
AND CITY = @CITY)
PRINT ('COUNT ROWS: ' + CONVERT(CHAR(6),@EXISTS))

IF (CONVERT(INT,@EXISTS) > 0)               
BEGIN
    PRINT ('RECORD EXISTS')
    SET @ADD_ID = (SELECT ID FROM CARETEAMZ..ADDRESS_BOOK WHERE HOME_PHONE = @HOMEPHONE 
                    AND COMPLEX_NUMBER = @COMP_NUM
                    AND COMPLEX_NAME = @COMP_NAME
                    AND STREET_NUMBER = @STREET_NUM
                    AND STREET_NAME = @STREET_NAME
                    AND STREET_TYPE = @STREET_TYPE
                    AND SUBURB = @SUB
                    AND CITY = @CITY) 
    PRINT ('ADDRESS ID: ' + CONVERT(CHAR(6),@ADD_ID))
END
ELSE
BEGIN
    PRINT ('RECORD DOES NOT EXIST')
    INSERT INTO CARETEAMZ..ADDRESS_BOOK (HOME_PHONE, COMPLEX_NUMBER, COMPLEX_NAME, STREET_NUMBER, STREET_NAME,STREET_TYPE, SUBURB, CITY)
    VALUES (@HOMEPHONE, @COMP_NUM,@COMP_NAME, @STREET_NUM,@STREET_NAME,@STREET_TYPE,@SUB,@CITY)

    SET @ADD_ID = (SELECT ID FROM CARETEAMZ..ADDRESS_BOOK WHERE HOME_PHONE = @HOMEPHONE 
                    AND COMPLEX_NUMBER = @COMP_NUM
                    AND COMPLEX_NAME = @COMP_NAME
                    AND STREET_NUMBER = @STREET_NUM
                    AND STREET_NAME = @STREET_NAME
                    AND STREET_TYPE = @STREET_TYPE
                    AND SUBURB = @SUB
                    AND CITY = @CITY) 
    PRINT ('ADDRESS ID: ' + CONVERT(char(6),@ADD_ID))
END

这是我的完整光标

DECLARE @COMP AS VARCHAR(500)
DECLARE @COMP_LENGTH AS INT
DECLARE @COMP_INDEX AS INT
DECLARE @COMP_NUM AS VARCHAR(50)
DECLARE @COMP_NAME AS VARCHAR(100)
DECLARE @STREET AS VARCHAR(500)
DECLARE @STREET_LENGTH AS INT
DECLARE @STREET_INDEX AS INT
DECLARE @STREET_NUM AS VARCHAR(50)
DECLARE @STREET_NAME AS VARCHAR(100)
DECLARE @STREET_VALUE AS VARCHAR(200)
DECLARE @STREET_TYPE AS INT
DECLARE @SUB AS VARCHAR(100)
DECLARE @CITY AS VARCHAR(100)
DECLARE @HOMEPHONE AS VARCHAR(100)
SET @CITY = 'JOHANNESBURG'

DECLARE ADD_CURSOR CURSOR FOR SELECT [HOME],[COMPLEX], [STREET] FROM #TEMPADD
OPEN ADD_CURSOR
FETCH NEXT FROM ADD_CURSOR INTO @HOMEPHONE,@COMP,@STREET;
    WHILE @@FETCH_STATUS = 0   
        BEGIN
            --COMPLEX DETAILS
            PRINT('-- COMPLEX DETAILS --')
            PRINT(@COMP) 
            SET @COMP_INDEX = CHARINDEX(',',@COMP)
            PRINT(@COMP_INDEX)
            IF (@COMP_INDEX > 0)
            BEGIN
                    PRINT('COMPLEX TRUE')
                    SET @COMP_NUM = LTRIM(RTRIM((SUBSTRING(@COMP,0,@COMP_INDEX))))
                    SET @COMP_NAME = LTRIM(RTRIM((SUBSTRING(@COMP,@COMP_INDEX+1,LEN(@COMP)))))                  
            END
            ELSE 
            BEGIN 
                    PRINT('COMPLEX FALSE')
                    SET @COMP_NUM = NULL
                    SET @COMP_NAME = NULL
            END
            PRINT('COMPLEX NUMBER: ' + CONVERT(VARCHAR(50),@COMP_NUM))
            PRINT('COMPLEX NAME: ' + @COMP_NAME)

            -- STREET ADDRESS
            PRINT('-- STREET NAME --')
            PRINT(@STREET) 
            SET @STREET_INDEX = CHARINDEX(',',@STREET)
            PRINT(@STREET_INDEX)
            IF (@STREET_INDEX > 0)
            BEGIN
                    PRINT('STREET TRUE')
                    SET @STREET_NUM = LTRIM(RTRIM((SUBSTRING(@STREET,0,@STREET_INDEX))))
                    SET @STREET = (SUBSTRING(@STREET,@STREET_INDEX+1,LEN(@STREET)))
                    PRINT(@STREET)
                    SET @STREET_INDEX = CHARINDEX(',',@STREET)
                    SET @STREET_NAME = LTRIM(RTRIM((SUBSTRING(@STREET,0,CHARINDEX(' ',@STREET)))))
                    SET @SUB = LTRIM(RTRIM((SUBSTRING(@STREET,@STREET_INDEX+1,LEN(@STREET)))))
                    SET @STREET_VALUE = SUBSTRING(@STREET,0,CHARINDEX(',',@STREET))
                    SET @STREET_TYPE = (SELECT ID FROM CARETEAMZ..STREET_TYPE WHERE STREET_TYPE = RTRIM(LTRIM((SUBSTRING(@STREET_VALUE,LEN(@STREET_VALUE)-CHARINDEX(' ',REVERSE(@STREET_VALUE))+2,LEN(@STREET_VALUE)-CHARINDEX(',',@STREET_VALUE))))))                  
            END
            ELSE 
            BEGIN 
                    PRINT('STREET FALSE')
                    SET @STREET_NUM = NULL
                    SET @STREET_NAME = NULL
                    SET @STREET_TYPE = NULL
                    SET @SUB = NULL
            END
            PRINT('STREET NUMBER: ' + @STREET_NUM)
            PRINT('STREET NAME: ' + @STREET_NAME)
            PRINT('STREET TYPE: ' + CONVERT(VARCHAR(10),@STREET_TYPE))
            PRINT('SUBURB: ' + @SUB)

            --CHECK IF THE ADDRESS / PHONE NUMBER ALREADY EXISTS IN THE DB
            DECLARE @ADD_ID AS INT
            DECLARE @EXISTS AS INT

            SET @EXISTS = (SELECT COUNT(ID) FROM CARETEAMZ..ADDRESS_BOOK WHERE HOME_PHONE = @HOMEPHONE 
            AND COMPLEX_NUMBER = @COMP_NUM
            AND COMPLEX_NAME = @COMP_NAME
            AND STREET_NUMBER = @STREET_NUM
            AND STREET_NAME = @STREET_NAME
            AND STREET_TYPE = @STREET_TYPE
            AND SUBURB = @SUB
            AND CITY = @CITY)
            PRINT ('COUNT ROWS: ' + CONVERT(CHAR(6),@EXISTS))
            IF (@EXISTS > 0)                
            BEGIN
                PRINT ('RECORD EXISTS')
                SET @ADD_ID = (SELECT ID FROM CARETEAMZ..ADDRESS_BOOK WHERE HOME_PHONE = @HOMEPHONE 
                                AND COMPLEX_NUMBER = @COMP_NUM
                                AND COMPLEX_NAME = @COMP_NAME
                                AND STREET_NUMBER = @STREET_NUM
                                AND STREET_NAME = @STREET_NAME
                                AND STREET_TYPE = @STREET_TYPE
                                AND SUBURB = @SUB
                                AND CITY = @CITY)

                PRINT ('ADDRESS ID: ' + CONVERT(CHAR(6),@ADD_ID))
            END
            ELSE
            BEGIN
                PRINT ('RECORD DOES NOT EXIST')
                INSERT INTO CARETEAMZ..ADDRESS_BOOK (HOME_PHONE, COMPLEX_NUMBER, COMPLEX_NAME, STREET_NUMBER, STREET_NAME,STREET_TYPE, SUBURB, CITY)
                VALUES (@HOMEPHONE, @COMP_NUM,@COMP_NAME, @STREET_NUM,@STREET_NAME,@STREET_TYPE,@SUB,@CITY)

                SET @ADD_ID = (SELECT ID FROM CARETEAMZ..ADDRESS_BOOK WHERE HOME_PHONE = @HOMEPHONE 
                                AND COMPLEX_NUMBER = @COMP_NUM
                                AND COMPLEX_NAME = @COMP_NAME
                                AND STREET_NUMBER = @STREET_NUM
                                AND STREET_NAME = @STREET_NAME
                                AND STREET_TYPE = @STREET_TYPE
                                AND SUBURB = @SUB
                                AND CITY = @CITY) 

                PRINT ('ADDRESS ID: ' + CONVERT(char(6),@ADD_ID))

            END

            --FETCH NEXT
            FETCH NEXT FROM ADD_CURSOR INTO @HOMEPHONE,@COMP, @STREET;
        END
CLOSE ADD_CURSOR;
DEALLOCATE ADD_CURSOR;

好的，所以我已经查看了 @HLGEM 和 @HLGEM 。 @kuru kuru pa 的答案。 @HLGEM 这只是更大游标的一小部分，不幸的是 MERGER SQL 无法满足我当前的需求。我不担心性能，因为这只是将数据导入到我的数据库中。

@kuru kuru pa 的答案很好，但不幸的是我似乎仍然再次遇到同样的问题。我第一次运行光标时它运行 100%。插入所有值，第二次它应该只返回 ID，但 id 不返回任何内容并插入重复项。

下面是 2 个带有一些虚拟数据的表创建语句。

临时表

CREATE TABLE #TEMPADD
(
    HOME VARCHAR(100) NULL,
    COMPLEX VARCHAR(100) NULL,
    STREET VARCHAR(100) NULL,
)

INSERT INTO #TEMPADD (HOME,COMPLEX,STREET) VALUES ('011 679 6787',  '32,Tagaste',   '150,Willson Street, Land')
INSERT INTO #TEMPADD (HOME,COMPLEX,STREET) VALUES ('011 679 1909',  NULL,   '29,Bunkara Street, Rio')
INSERT INTO #TEMPADD (HOME,COMPLEX,STREET) VALUES ('011 689 2630',  NULL,   '275,Kings Lynne Road, Glen')
INSERT INTO #TEMPADD (HOME,COMPLEX,STREET) VALUES (NULL,    NULL,   '275,Kings Lynne Road, Glen')
INSERT INTO #TEMPADD (HOME,COMPLEX,STREET) VALUES ('011 799 5917',  '5,The Vineyard',   '45,Hilary Road, Ridge')
INSERT INTO #TEMPADD (HOME,COMPLEX,STREET) VALUES (NULL,    '5,The Vineyard',   '45,Hilary Road, Ridge')
INSERT INTO #TEMPADD (HOME,COMPLEX,STREET) VALUES ('011 679 5857',  NULL,   '11A,Alexandra Street, Florida')
INSERT INTO #TEMPADD (HOME,COMPLEX,STREET) VALUES ('011 679 5857',  NULL,   '11A,Alexandra Street, Florida')
INSERT INTO #TEMPADD (HOME,COMPLEX,STREET) VALUES ('011 679 3225',  NULL,   '752, Without Avenue, Weltevreden')
INSERT INTO #TEMPADD (HOME,COMPLEX,STREET) VALUES ('011 679 8909',  NULL,   '18,Smit Street,Land')
INSERT INTO #TEMPADD (HOME,COMPLEX,STREET) VALUES ('011 679 8909',  '512,Athenian View',    '158,Smit Street, Fairland')
INSERT INTO #TEMPADD (HOME,COMPLEX,STREET) VALUES (NULL,    '741,Eagle Trace Landing',  'Eagle Canyon Golf Estate, Honey')
INSERT INTO #TEMPADD (HOME,COMPLEX,STREET) VALUES (NULL,    '741,Eagle Trace Landing',  'Eagle Canyon Golf Estate, Honey')
INSERT INTO #TEMPADD (HOME,COMPLEX,STREET) VALUES (NULL,    '741,Eagle Trace Landing',  'Eagle Canyon Golf Estate, Honey')
INSERT INTO #TEMPADD (HOME,COMPLEX,STREET) VALUES (NULL,    '741,Eagle Trace Landing',  'Eagle Canyon Golf Estate, Honey')
INSERT INTO #TEMPADD (HOME,COMPLEX,STREET) VALUES (NULL,    '741,Eagle Trace Landing',  'Eagle Canyon Golf Estate, Honey')
INSERT INTO #TEMPADD (HOME,COMPLEX,STREET) VALUES (NULL,    '741,Eagle Trace Landing',  'Eagle Canyon Golf Estate, Honey')
INSERT INTO #TEMPADD (HOME,COMPLEX,STREET) VALUES (NULL,    NULL,   '106A,3rd Avenue, Land')

地址表

CREATE TABLE [dbo].[ADDRESS_BOOK](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [PoBox] [varchar](50) NULL,
    [Complex_Number] [varchar](50) NULL,
    [Complex_Name] [varchar](100) NULL,
    [Street_Number] [varchar](50) NULL,
    [Street_Name] [varchar](100) NULL,
    [Street_Type] [int] NULL,
    [Suburb] [varchar](100) NULL,
    [City] [varchar](50) NULL,
    [Code] [varchar](50) NULL,
    [Home_Phone] [varchar](50) NULL
) ON [PRIMARY]

原文

I have a #temptable with address information in it that is being read using a cursor. The cursor works great. The if statement in the cursor works 100% for the first record in the #table but for the second record it seems to always return false.

The idea of the if statement is to check in the value exists in the address table in the db and then if it does just get the id. If it doesn't then insert the row and get the id. Stand alone the if statement works 100%. The moment I add it into the cursor it breaks. Its almost as if the variables are being over written somehow.

So if the same row is contained in my #table the row still gets inserted twice into the address_table instead of just once. First time the address is inserted if it does not exist, 2nd time the id of the address in the table should be retured

I have taken out the print statements where i do a convert() and it doesn't seem to have made a difference.

This is the stand alone if statement.

DECLARE @COMP_NUM AS VARCHAR(100) = '4'
DECLARE @COMP_NAME AS VARCHAR(100) = 'TAGASTE'
DECLARE @STREET_NUM AS VARCHAR(100) = '150'
DECLARE @STREET_NAME AS VARCHAR(100) = 'WILLSON'
DECLARE @STREET_TYPE AS INT = 1
DECLARE @SUB AS VARCHAR(100) = 'FAIRLANDS'
DECLARE @CITY AS VARCHAR(100) = 'JOHANNESBURG'
DECLARE @HOMEPHONE AS VARCHAR(100) = '0112355566'
DECLARE @EXISTS AS INT
DECLARE @ADD_ID AS INT

SET @EXISTS = (SELECT COUNT(ID) FROM CARETEAMZ..ADDRESS_BOOK WHERE HOME_PHONE=@HOMEPHONE 
AND COMPLEX_NUMBER = @COMP_NUM
AND COMPLEX_NAME = @COMP_NAME
AND STREET_NUMBER = @STREET_NUM
AND STREET_NAME = @STREET_NAME
AND STREET_TYPE = @STREET_TYPE
AND SUBURB = @SUB
AND CITY = @CITY)
PRINT ('COUNT ROWS: ' + CONVERT(CHAR(6),@EXISTS))

IF (CONVERT(INT,@EXISTS) > 0)               
BEGIN
    PRINT ('RECORD EXISTS')
    SET @ADD_ID = (SELECT ID FROM CARETEAMZ..ADDRESS_BOOK WHERE HOME_PHONE = @HOMEPHONE 
                    AND COMPLEX_NUMBER = @COMP_NUM
                    AND COMPLEX_NAME = @COMP_NAME
                    AND STREET_NUMBER = @STREET_NUM
                    AND STREET_NAME = @STREET_NAME
                    AND STREET_TYPE = @STREET_TYPE
                    AND SUBURB = @SUB
                    AND CITY = @CITY) 
    PRINT ('ADDRESS ID: ' + CONVERT(CHAR(6),@ADD_ID))
END
ELSE
BEGIN
    PRINT ('RECORD DOES NOT EXIST')
    INSERT INTO CARETEAMZ..ADDRESS_BOOK (HOME_PHONE, COMPLEX_NUMBER, COMPLEX_NAME, STREET_NUMBER, STREET_NAME,STREET_TYPE, SUBURB, CITY)
    VALUES (@HOMEPHONE, @COMP_NUM,@COMP_NAME, @STREET_NUM,@STREET_NAME,@STREET_TYPE,@SUB,@CITY)

    SET @ADD_ID = (SELECT ID FROM CARETEAMZ..ADDRESS_BOOK WHERE HOME_PHONE = @HOMEPHONE 
                    AND COMPLEX_NUMBER = @COMP_NUM
                    AND COMPLEX_NAME = @COMP_NAME
                    AND STREET_NUMBER = @STREET_NUM
                    AND STREET_NAME = @STREET_NAME
                    AND STREET_TYPE = @STREET_TYPE
                    AND SUBURB = @SUB
                    AND CITY = @CITY) 
    PRINT ('ADDRESS ID: ' + CONVERT(char(6),@ADD_ID))
END

This is my full cursor

DECLARE @COMP AS VARCHAR(500)
DECLARE @COMP_LENGTH AS INT
DECLARE @COMP_INDEX AS INT
DECLARE @COMP_NUM AS VARCHAR(50)
DECLARE @COMP_NAME AS VARCHAR(100)
DECLARE @STREET AS VARCHAR(500)
DECLARE @STREET_LENGTH AS INT
DECLARE @STREET_INDEX AS INT
DECLARE @STREET_NUM AS VARCHAR(50)
DECLARE @STREET_NAME AS VARCHAR(100)
DECLARE @STREET_VALUE AS VARCHAR(200)
DECLARE @STREET_TYPE AS INT
DECLARE @SUB AS VARCHAR(100)
DECLARE @CITY AS VARCHAR(100)
DECLARE @HOMEPHONE AS VARCHAR(100)
SET @CITY = 'JOHANNESBURG'

DECLARE ADD_CURSOR CURSOR FOR SELECT [HOME],[COMPLEX], [STREET] FROM #TEMPADD
OPEN ADD_CURSOR
FETCH NEXT FROM ADD_CURSOR INTO @HOMEPHONE,@COMP,@STREET;
    WHILE @@FETCH_STATUS = 0   
        BEGIN
            --COMPLEX DETAILS
            PRINT('-- COMPLEX DETAILS --')
            PRINT(@COMP) 
            SET @COMP_INDEX = CHARINDEX(',',@COMP)
            PRINT(@COMP_INDEX)
            IF (@COMP_INDEX > 0)
            BEGIN
                    PRINT('COMPLEX TRUE')
                    SET @COMP_NUM = LTRIM(RTRIM((SUBSTRING(@COMP,0,@COMP_INDEX))))
                    SET @COMP_NAME = LTRIM(RTRIM((SUBSTRING(@COMP,@COMP_INDEX+1,LEN(@COMP)))))                  
            END
            ELSE 
            BEGIN 
                    PRINT('COMPLEX FALSE')
                    SET @COMP_NUM = NULL
                    SET @COMP_NAME = NULL
            END
            PRINT('COMPLEX NUMBER: ' + CONVERT(VARCHAR(50),@COMP_NUM))
            PRINT('COMPLEX NAME: ' + @COMP_NAME)

            -- STREET ADDRESS
            PRINT('-- STREET NAME --')
            PRINT(@STREET) 
            SET @STREET_INDEX = CHARINDEX(',',@STREET)
            PRINT(@STREET_INDEX)
            IF (@STREET_INDEX > 0)
            BEGIN
                    PRINT('STREET TRUE')
                    SET @STREET_NUM = LTRIM(RTRIM((SUBSTRING(@STREET,0,@STREET_INDEX))))
                    SET @STREET = (SUBSTRING(@STREET,@STREET_INDEX+1,LEN(@STREET)))
                    PRINT(@STREET)
                    SET @STREET_INDEX = CHARINDEX(',',@STREET)
                    SET @STREET_NAME = LTRIM(RTRIM((SUBSTRING(@STREET,0,CHARINDEX(' ',@STREET)))))
                    SET @SUB = LTRIM(RTRIM((SUBSTRING(@STREET,@STREET_INDEX+1,LEN(@STREET)))))
                    SET @STREET_VALUE = SUBSTRING(@STREET,0,CHARINDEX(',',@STREET))
                    SET @STREET_TYPE = (SELECT ID FROM CARETEAMZ..STREET_TYPE WHERE STREET_TYPE = RTRIM(LTRIM((SUBSTRING(@STREET_VALUE,LEN(@STREET_VALUE)-CHARINDEX(' ',REVERSE(@STREET_VALUE))+2,LEN(@STREET_VALUE)-CHARINDEX(',',@STREET_VALUE))))))                  
            END
            ELSE 
            BEGIN 
                    PRINT('STREET FALSE')
                    SET @STREET_NUM = NULL
                    SET @STREET_NAME = NULL
                    SET @STREET_TYPE = NULL
                    SET @SUB = NULL
            END
            PRINT('STREET NUMBER: ' + @STREET_NUM)
            PRINT('STREET NAME: ' + @STREET_NAME)
            PRINT('STREET TYPE: ' + CONVERT(VARCHAR(10),@STREET_TYPE))
            PRINT('SUBURB: ' + @SUB)

            --CHECK IF THE ADDRESS / PHONE NUMBER ALREADY EXISTS IN THE DB
            DECLARE @ADD_ID AS INT
            DECLARE @EXISTS AS INT

            SET @EXISTS = (SELECT COUNT(ID) FROM CARETEAMZ..ADDRESS_BOOK WHERE HOME_PHONE = @HOMEPHONE 
            AND COMPLEX_NUMBER = @COMP_NUM
            AND COMPLEX_NAME = @COMP_NAME
            AND STREET_NUMBER = @STREET_NUM
            AND STREET_NAME = @STREET_NAME
            AND STREET_TYPE = @STREET_TYPE
            AND SUBURB = @SUB
            AND CITY = @CITY)
            PRINT ('COUNT ROWS: ' + CONVERT(CHAR(6),@EXISTS))
            IF (@EXISTS > 0)                
            BEGIN
                PRINT ('RECORD EXISTS')
                SET @ADD_ID = (SELECT ID FROM CARETEAMZ..ADDRESS_BOOK WHERE HOME_PHONE = @HOMEPHONE 
                                AND COMPLEX_NUMBER = @COMP_NUM
                                AND COMPLEX_NAME = @COMP_NAME
                                AND STREET_NUMBER = @STREET_NUM
                                AND STREET_NAME = @STREET_NAME
                                AND STREET_TYPE = @STREET_TYPE
                                AND SUBURB = @SUB
                                AND CITY = @CITY)

                PRINT ('ADDRESS ID: ' + CONVERT(CHAR(6),@ADD_ID))
            END
            ELSE
            BEGIN
                PRINT ('RECORD DOES NOT EXIST')
                INSERT INTO CARETEAMZ..ADDRESS_BOOK (HOME_PHONE, COMPLEX_NUMBER, COMPLEX_NAME, STREET_NUMBER, STREET_NAME,STREET_TYPE, SUBURB, CITY)
                VALUES (@HOMEPHONE, @COMP_NUM,@COMP_NAME, @STREET_NUM,@STREET_NAME,@STREET_TYPE,@SUB,@CITY)

                SET @ADD_ID = (SELECT ID FROM CARETEAMZ..ADDRESS_BOOK WHERE HOME_PHONE = @HOMEPHONE 
                                AND COMPLEX_NUMBER = @COMP_NUM
                                AND COMPLEX_NAME = @COMP_NAME
                                AND STREET_NUMBER = @STREET_NUM
                                AND STREET_NAME = @STREET_NAME
                                AND STREET_TYPE = @STREET_TYPE
                                AND SUBURB = @SUB
                                AND CITY = @CITY) 

                PRINT ('ADDRESS ID: ' + CONVERT(char(6),@ADD_ID))

            END

            --FETCH NEXT
            FETCH NEXT FROM ADD_CURSOR INTO @HOMEPHONE,@COMP, @STREET;
        END
CLOSE ADD_CURSOR;
DEALLOCATE ADD_CURSOR;

Ok so i've look at both @HLGEM & @kuru kuru pa's answers. @HLGEM This is just a small part of a bigger cursor and unfortunately MERGER SQL won't work for my current needs. I'm not to worried about performance as this is just to import data into my db.

@kuru kuru pa's answer is a good one but unfortunately i still seem to be getting the same issue again. 1st time i run the cursor it runs 100%. Inserts all the values, 2nd time it should only return IDs but id doesn't return anything and inserts the duplicates.

Below are the 2 table create statements with some dummy data.

TEMP TABLE

CREATE TABLE #TEMPADD
(
    HOME VARCHAR(100) NULL,
    COMPLEX VARCHAR(100) NULL,
    STREET VARCHAR(100) NULL,
)

INSERT INTO #TEMPADD (HOME,COMPLEX,STREET) VALUES ('011 679 6787',  '32,Tagaste',   '150,Willson Street, Land')
INSERT INTO #TEMPADD (HOME,COMPLEX,STREET) VALUES ('011 679 1909',  NULL,   '29,Bunkara Street, Rio')
INSERT INTO #TEMPADD (HOME,COMPLEX,STREET) VALUES ('011 689 2630',  NULL,   '275,Kings Lynne Road, Glen')
INSERT INTO #TEMPADD (HOME,COMPLEX,STREET) VALUES (NULL,    NULL,   '275,Kings Lynne Road, Glen')
INSERT INTO #TEMPADD (HOME,COMPLEX,STREET) VALUES ('011 799 5917',  '5,The Vineyard',   '45,Hilary Road, Ridge')
INSERT INTO #TEMPADD (HOME,COMPLEX,STREET) VALUES (NULL,    '5,The Vineyard',   '45,Hilary Road, Ridge')
INSERT INTO #TEMPADD (HOME,COMPLEX,STREET) VALUES ('011 679 5857',  NULL,   '11A,Alexandra Street, Florida')
INSERT INTO #TEMPADD (HOME,COMPLEX,STREET) VALUES ('011 679 5857',  NULL,   '11A,Alexandra Street, Florida')
INSERT INTO #TEMPADD (HOME,COMPLEX,STREET) VALUES ('011 679 3225',  NULL,   '752, Without Avenue, Weltevreden')
INSERT INTO #TEMPADD (HOME,COMPLEX,STREET) VALUES ('011 679 8909',  NULL,   '18,Smit Street,Land')
INSERT INTO #TEMPADD (HOME,COMPLEX,STREET) VALUES ('011 679 8909',  '512,Athenian View',    '158,Smit Street, Fairland')
INSERT INTO #TEMPADD (HOME,COMPLEX,STREET) VALUES (NULL,    '741,Eagle Trace Landing',  'Eagle Canyon Golf Estate, Honey')
INSERT INTO #TEMPADD (HOME,COMPLEX,STREET) VALUES (NULL,    '741,Eagle Trace Landing',  'Eagle Canyon Golf Estate, Honey')
INSERT INTO #TEMPADD (HOME,COMPLEX,STREET) VALUES (NULL,    '741,Eagle Trace Landing',  'Eagle Canyon Golf Estate, Honey')
INSERT INTO #TEMPADD (HOME,COMPLEX,STREET) VALUES (NULL,    '741,Eagle Trace Landing',  'Eagle Canyon Golf Estate, Honey')
INSERT INTO #TEMPADD (HOME,COMPLEX,STREET) VALUES (NULL,    '741,Eagle Trace Landing',  'Eagle Canyon Golf Estate, Honey')
INSERT INTO #TEMPADD (HOME,COMPLEX,STREET) VALUES (NULL,    '741,Eagle Trace Landing',  'Eagle Canyon Golf Estate, Honey')
INSERT INTO #TEMPADD (HOME,COMPLEX,STREET) VALUES (NULL,    NULL,   '106A,3rd Avenue, Land')

ADDRESS TABLE

CREATE TABLE [dbo].[ADDRESS_BOOK](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [PoBox] [varchar](50) NULL,
    [Complex_Number] [varchar](50) NULL,
    [Complex_Name] [varchar](100) NULL,
    [Street_Number] [varchar](50) NULL,
    [Street_Name] [varchar](100) NULL,
    [Street_Type] [int] NULL,
    [Suburb] [varchar](100) NULL,
    [City] [varchar](50) NULL,
    [Code] [varchar](50) NULL,
    [Home_Phone] [varchar](50) NULL
) ON [PRIMARY]

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

浮云落日 2024-12-10 02:18:41

你到底为什么要使用光标来执行此操作？建议您阅读这篇文章，了解使用基于集合的技术有多么容易（以及更快）：

http://wiki.lessthandot.com/index.php/Cursors_and_How_to_Avoid_Them

回复收藏 0 原文

感性 2024-12-10 02:18:41

有很多代码需要阅读...

首先观察（不是您问题的答案，而是 FWIW）是您可能使用了错误的工具来完成这项工作。不确定这里是否需要光标。无论如何......

现在只是抽查......（除非你想添加@martin建议的创建表脚本......（如果你这样做，我会给你一个更彻底的答案）

你对@Exists的使用是多余的基本上，您使用 @Exists 来告诉您 @Add_ID 是否存在。这是一种间接/冗余的方法，

If @Exists exists, then @Add_ID exists.

您可以使用更无聊的同义反复（直接方法）：

If @Add_ID exists, then @Add_ID exists.

所以您的代码可能是这样的：

~~设置@存在= ...~~
~~如果@Exits> 0...~~

SET @ADD_ID = (SELECT ID FROM CARETEAMZ..ADDRESS_BOOK WHERE HOME_PHONE = @HOMEPHONE AND ...)
IF @ADD_ID IS NOT NULL
BEGIN
   PRINT(...)
END
ELSE...

That's a lot of code to read through...

First observation (not an answer to your question, but FWIW) is that you might be using the wrong tool for the job. Not sure a cursor is necessary here. Anyway...

Just spot-checking for now...(unless you want to add the create table script @martin suggested...(i'll give you a more thorough answer if you do)

Your use of @Exists is redundant. Basically, you're using @Exists to tell you if @Add_ID will exist. It's an indirect / redundant approach.

If @Exists exists, then @Add_ID exists.

You can use a much more boring tautology (direct approach):

If @Add_ID exists, then @Add_ID exists.

So your code could be this:

~~SET @Exists = ...~~
~~If @Exits > 0...~~

SET @ADD_ID = (SELECT ID FROM CARETEAMZ..ADDRESS_BOOK WHERE HOME_PHONE = @HOMEPHONE AND ...)
IF @ADD_ID IS NOT NULL
BEGIN
   PRINT(...)
END
ELSE...

回复收藏 0 原文

~没有更多了~

关于作者

人生百味

暂无简介

0 文章

0 评论

24 人气

关注发私信

友情链接

文江博客

Microsoft SQL Server 2008 游标

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

评论（2）

关于作者

相关话题

热门标签

推荐作者

離殇

小姐丶请自重

Aik

国产ˉ祖宗

猥琐帝

半仙

友情链接

Microsoft SQL Server 2008 游标

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

评论（2）

关于作者

相关话题

热门标签

推荐作者

離殇

小姐丶请自重

Aik

国产ˉ祖宗

猥琐帝

半仙

友情链接

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。