Solved

IF not exists query

Posted on 2014-12-15
2
81 Views
Last Modified: 2014-12-15
Can someone take a look at my sql below. It is probably more like Pseudo code right now, because
I did not include all of the script. Take a look at lines 14 and lines 17. I do an if exists and a select into PersonId
Can I take the value in PersonId at line 14 and put it into the value TargetTable.PersonId at line 17 ?


1  OPEN CURS_EMP
2  @BreakOutOfLoop INT

3  SELECT @lchFITS_SUFFIX = CONVERT(INT, @lchSuffix)
4  FETCH NEXT FROM CURS_EMP
5  INTO @lchPSUS_USER_NUM, @lchFITS_STATE_CD, @lchFITS_PREFIX
6  SELECT @lchFITS_SUFFIX = @lchFITS_SUFFIX+1
7

8 WHILE @@FETCH_STATUS = 0
9 BEGIN
10      SET @SnafterIdIsUnique = 0
11
12      WHILE(@SnafterIdIsUnique = 0)
13       BEGIN
14          IF NOT EXISTS(SELECT PersonsId =  GetRandomId())
15          BEGIN
16                UPDATE TargetTable
17                SET TargetTable.PersonId = PersonId
18                SET @SnafterIdIsUnique = 1

19          END
20       END
0
Comment
Question by:brgdotnet
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 7

Accepted Solution

by:
Phil Davidson earned 500 total points
ID: 40501852
What version of SQL Server is this?  There is a phrase "my sql" in the comment.

I would rewrite lines 14 through 18 to this:

SET @specialVar = ISNULL(PersonID, GetRandomID())
UPDATE TargetTable
                SET TargetTable.PersonId = @specialVar
                SET @SnafterIdIsUnique = 1

Open in new window


If it is MySQL, the ISNULL should be IFNULL.

Lines 8 through 18 as they were written seem verbose.  Maybe there is a reason to do it like that.
0
 
LVL 2

Author Comment

by:brgdotnet
ID: 40501885
No not MySQL. I was taking sql in the possessive sense, like "My sql script"
0

Featured Post

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question