none
Loop through to check if pram=ID or if it Exists. if so insert, if not skip to next ID

    Question

  • I am new to SQL statements and I have an application that a user can insert an Alert and delete an Alert so the ID skips around. ex. 10, 11, 14, 18, 20, 21, 22, 23... and so on. I have got my query to execute and populate the alerts but it is populating duplicates. Where the ID skips around it is populating the previous Alert as it loops. I need it to see if the Alert ID exists first, then loop through to insert it, then either go to next ID or see if the next number Exists in the IDs. I have tried a bunch of statements, most may look alittle silly but as I said I am new to SQL statements. But I have only listed a few below. Alittle assistance would be greatly appreciated.

    I would like to find a solution as such if @i = id then loop. but I am willing to try anythng at this point.

    Again thanks

    DECLARE @COUNT INT

    --DECLARE @i_alertid INT

    DECLARE @MAXID INT

    SET @COUNT = 1

    ----------------Things I have tried but no luck---------------------

    --IF EXISTS (select max(alertid) from client_alerts)

    --IF Exists(select * from client_alert where scheduleid = @count)

    --set @maxid = (select max(scheduleid)from client_alerts)

    -----------------------------------------------------------------------------

    While (@count<=@maxid)

    BEGIN

    --------Select statements for  Gathering my table inro for insert

                                BEGIN

                                  insert into NotesHistory (Oppid, clientid, userid, notedate, note)

                                        values(@oppid, @clientID, @empid, @alertdate, @alertdesc)

                                  END

     SET @count = (@count + 1)

     END

    Thursday, November 22, 2012 4:10 AM

Answers

  • Where does this below record come from?

    @oppid, @clientID, @empid, @alertdate, @alertdesc

    You can have a pre-check to see if the relevant record does not exist, then you do the insert.


    Many Thanks & Best Regards, Hua Min


    Friday, November 23, 2012 1:34 AM
  • Your while loop should be checking for the existence of an anti join of your client alerts table and your NotesHistory table. I am not sure what the meaning of your IDs are so you will have to figure out if "some id" is "some id" or "some id" and "some other id" and etc.

    WHILE EXISTS (SELECT * FROM client_alters T1 LEFT JOIN NotesHistory T2 ON T1."some id" = T2. "some id" WHERE T2.some id IS NULL)

    This will cause your inserter loop to run as long as there are rows in client alerts without "some id" representing them in the NotesHistory table. Of course you should also make sure that the select statements you are assigning your variables with actually insert "some id" so that the loop eventually runs out of "some id" in client alert and not in NotesHistory.


    If you're happy and you know it vote and mark.

    Friday, November 23, 2012 3:20 AM

All replies

  • Try

    DECLARE @COUNT INT
    --DECLARE @i_alertid INT
    DECLARE @MAXID INT
    SET @COUNT = 1
    ----------------Things I have tried but no luck---------------------
    --IF EXISTS (select max(alertid) from client_alerts)
    --IF Exists(select * from client_alert where scheduleid = @count)
    --set @maxid = (select max(scheduleid)from client_alerts)
    -----------------------------------------------------------------------------
    While (@count<=@maxid)
    BEGIN
    --------Select statements for  Gathering my table inro for insert
                IF Exists(select * from client_alert where scheduleid = @count)
                                BEGIN
                                begin tran
                                  insert into NotesHistory (Oppid, clientid, userid, notedate, note)
                                        values(@oppid, @clientID, @empid, @alertdate, @alertdesc)
                                commit tran
                                END
     SET @count = (@count + 1)
     END


    Many Thanks & Best Regards, Hua Min



    Thursday, November 22, 2012 4:24 AM
  • try the below one

    DECLARE @COUNT INT
    --DECLARE @i_alertid INT
    DECLARE @MAXID INT
    SET @COUNT = 1
    ----------------Things I have tried but no luck---------------------
    --IF EXISTS (select max(alertid) from client_alerts)
    --IF Exists(select * from client_alert where scheduleid = @count)
    --set @maxid = (select max(scheduleid)from client_alerts)
    -----------------------------------------------------------------------------
    While (@count<=@maxid)
    BEGIN
    --------Select statements for  Gathering my table inro for insert
                      IF ((select scheduleid from client_alert where scheduleid = @count)) = @count
                                BEGIN
                                begin tran
                                  insert into NotesHistory (Oppid, clientid, userid, notedate, note)
                                        values(@oppid, @clientID, @empid, @alertdate, @alertdesc)
                                commit tran
                                END
                      
     SET @count = (@count + 1)
     END
    --------------------------------
    ------------------------------------


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, November 22, 2012 4:54 AM
  • Odd, I still seem to be getting a few duplicates for some reason? It actually cut back on the duplicates but didnt get rid of them all. I just wonder if the problem isn't in my "select" statements to gather the table info? Thank you tho, your solution did essentially work, I just believe there may be another problem, maybe?


    You still need to check against every scheduleid one after another.

    Many Thanks & Best Regards, Hua Min

    Thursday, November 22, 2012 5:22 AM
  • I am sorry to ask you this but can you explain alittle more on what you mean "check against scheduleid one after another"?
    Thursday, November 22, 2012 5:47 AM
  • I mean you need to one by one validate/check against the scheduleid.

    Many Thanks & Best Regards, Hua Min


    Thursday, November 22, 2012 6:01 AM
  • Can I bother you to show me an example statement of what you are meaning. I have tried alittle of everythign between this morning and last night and am sill stuck with the proble of the duplicates.

    Thanks again

    Thursday, November 22, 2012 2:50 PM
  • Which is duplicated in your case? Do you want to pass alert ID to the above codes?

    Many Thanks & Best Regards, Hua Min

    Thursday, November 22, 2012 3:19 PM
  • I hope this answers your questions effeciantly enough to help. I am trying to import the existing alerts from "Alerts" to the "noteshistory" table there is only a primary key in "Alerts" called "scheduleid". Which can be valid or not depending if the user deleted the alert, so the ID skips around. I was thinking maybe for the loop counter to get to 1 and see if the "scheduleid" exists, --> if true--> select the alert, clientid, oppid from the id and insert into Noteshistory.

    It is still duplicating rows, not quite as many since I applied your previous solution but I am still gettign about 3000 extra rows. Like I said I hope this answers your questions and there is some hope in assisting me with the solution.
    the rows are duplicating themselves more towards the end of the query

    Thursday, November 22, 2012 4:21 PM
  • Where does this below record come from?

    @oppid, @clientID, @empid, @alertdate, @alertdesc

    You can have a pre-check to see if the relevant record does not exist, then you do the insert.


    Many Thanks & Best Regards, Hua Min


    Friday, November 23, 2012 1:34 AM
  • >> I am new to SQL and I have an application .. <<

    They want you fail. Your posting makes no sense in Relational terms. Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

    This is minimal polite behavior on SQL forums.

    In RDBMS, an identifier is a subset of the attributes of an entity. Think about the VIN on an automobile; it is part of the entity. If this was geography forum, the question you asked would have been “How many turtles support the flat earth on the back of the four elephants that hold up the world?” in that environment.

    You need to get a BASIC education in RDBMS first, then post here. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, November 23, 2012 2:40 AM
  • Your while loop should be checking for the existence of an anti join of your client alerts table and your NotesHistory table. I am not sure what the meaning of your IDs are so you will have to figure out if "some id" is "some id" or "some id" and "some other id" and etc.

    WHILE EXISTS (SELECT * FROM client_alters T1 LEFT JOIN NotesHistory T2 ON T1."some id" = T2. "some id" WHERE T2.some id IS NULL)

    This will cause your inserter loop to run as long as there are rows in client alerts without "some id" representing them in the NotesHistory table. Of course you should also make sure that the select statements you are assigning your variables with actually insert "some id" so that the loop eventually runs out of "some id" in client alert and not in NotesHistory.


    If you're happy and you know it vote and mark.

    Friday, November 23, 2012 3:20 AM
  • Thank you for being patient with me and guiding me, I know it takes a lot for people to understand that not everyone can know or understand everything. (@oppid, @clientid, @empid, ect) were the select statements I was using.  Thanks for your assistance in this problem and I wont bother you any longer, have a Happy Thanksgiving!

    WHILE @Count >= @maxid 

           Select @clientid = clientid from client_alerts where scheduleid = @count and completed=1

            select @oppid = oppid from client_alerts where scheduleid = @count and completed = 1

           select @empid = employeeid from client_alerts a join client_alert_contacts b on a.scheduleid    =  b.schedid and completed = 1

        IF Exists(select * from client_alert where scheduleid = @count)
                                BEGIN
                                begin tran
                                  insert into NotesHistory (Oppid, clientid, userid, notedate, note)
                                        values(@oppid, @clientID, @empid, @alertdate, @alertdesc)
                                commit tran
                                END
    SET @count = (@count + 1)
    END

    Friday, November 23, 2012 6:33 AM