Professional Web Applications Themes

TSQL Query - Microsoft SQL / MS SQL Server

Hi Friends, I am looping the below query 100,000 times because I am unable to write a 'SET BASED' query. Can any one help me out to write simple set based query. Thank you in advance. ----- WHILE (Event_ID < 100000) BEGIN SET execSQL = ' IF EXISTS ( SELECT * FROM GFDM.Event WHERE Event_ID = ' + CAST(Event_ID AS VARCHAR) + ' AND BegSta = (SELECT MAX(EndSta) FROM GFDM.StationSeries WHERE Pipeline_ID = (SELECT Pipeline_ID FROM GFDM.Event WHERE Event_ID = ' + CAST (Event_ID AS VARCHAR) + ') ) ) BEGIN UPDATE ET SET BegSS_ID = SS.SS_ID FROM GFDM.Event ET, ...

  1. #1

    Default TSQL Query

    Hi Friends, I am looping the below query 100,000 times
    because I am unable to write a 'SET BASED' query. Can any
    one help me out to write simple set based query.
    Thank you in advance.
    -----
    WHILE (Event_ID < 100000)
    BEGIN

    SET execSQL =
    ' IF EXISTS ( SELECT * FROM GFDM.Event WHERE Event_ID = '
    + CAST(Event_ID AS VARCHAR) + ' AND BegSta =
    (SELECT MAX(EndSta)
    FROM GFDM.StationSeries WHERE Pipeline_ID =
    (SELECT
    Pipeline_ID FROM GFDM.Event WHERE Event_ID = ' + CAST
    (Event_ID AS VARCHAR) + ') ) )
    BEGIN
    UPDATE ET SET BegSS_ID = SS.SS_ID
    FROM GFDM.Event ET, GFDM.StationSeries SS
    WHERE SS.PIPELINE_ID = ET.PIPELINE_ID
    AND ET.BegSta > SS.BegSta
    AND ET.BegSta <= SS.EndSta
    AND ET.StationType_GL IN (1582, 1583)
    AND ET.Event_ID = ' + CAST(Event_ID AS VARCHAR) + '
    AND ET.BegSta = (SELECT MAX(EndSta) FROM
    GFDM.StationSeries
    WHERE Pipeline_ID = (SELECT Pipeline_ID FROM
    GFDM.Event
    WHERE Event_ID = ' + CAST(Event_ID AS VARCHAR)
    + ' ) )

    END '

    EXEC (execSQL)

    SET Event_ID = Event_ID + 1
    END
    ------

    Rayan Guest

  2. #2

    Default Re: TSQL Query

    Rayan,

    If you post full DDL (create table plus constraints) and some sample data then someone may be able to help. Unfortunately with what you have posted it is very difficult to see what you are trying to achieve. (Now someone will prove that wrong and write you the query!)

    Mike John

    "Rayan Yellina" <com> wrote in message news:02fe01c34bd5$bb62fdd0$gbl... 

    Mike Guest

  3. #3

    Default Re: TSQL Query

    Try this...

    First, Make a temp table with all of your maximums:

    SELECT Event_ID, PipeLine_ID, Max(ss.EndSta) as MaxSta
    INTO #StaMaximums
    FROM Event et, StationSeries ss
    WHERE et.PipeLineID = ss.PipeLineID
    GROUP BY Event_ID, PipeLine_ID

    Now a query like this should handle it:

    UPDATE Event
    SET BegSS_ID = ss.SS_ID
    From Event et, StationSeries ss, #StaMaximums csm
    Where et.PipeLine_ID = ss.PipeLine_ID
    AND csm.Event_ID = et.Event_ID
    AND csm.PipeLine_ID = et.PipeLine_ID
    AND et.BegSta = csm.MaxSta
    AND .... rest of your criteria




    "Mike John" <com> wrote in message
    news:phx.gbl...
    Rayan,

    If you post full DDL (create table plus constraints) and some sample data
    then someone may be able to help. Unfortunately with what you have posted it
    is very difficult to see what you are trying to achieve. (Now someone will
    prove that wrong and write you the query!)

    Mike John

    "Rayan Yellina" <com> wrote in message
    news:02fe01c34bd5$bb62fdd0$gbl... 


    Cole Guest

  4. #4

    Default Re: TSQL Query

    Yes sir, you are right. I realised it after I posted this
    message. Why I used the Dynamic SQL? is because it used to
    be Dynamic before (instead of "EVENT table" , it used to
    be dynamic). Yesterday, I replaced with just EVENT table
    because 99% of the times it will be Event table(which has
    100 times more records than the tables I use for another
    1%). So, I taught I will put EVENT (I forgot to remove the
    Dynamic SQL) and also if I will get another advantage by
    removing the Dynamic SQL, it will be much much faster. I
    will think of another SP (with dynamic SQL) for another 1%
    of the tables.
    I hope you it or not, Mr. Shelton, posted good work-
    around to this problem. I think his solution will be
    perfect for this query.
    I am very Thankful for the time you spent to help me.
    Thank you sir.

    Rayan.

     
    first simple 
    easier to read and 
    complex, and 
    because it is 
    statement. 
    become NULL 
    be raised 
    some assumptions. 
    Event_ID at 1. 
    table Event. 
    assumptions) 
    become NULL 
    be raised [/ref]
    any [/ref]
    = ' 
    >.
    >[/ref]
    Rayan Guest

Similar Threads

  1. How you convert this cfquery to TSQL?
    By roofusthedoofus in forum Coldfusion - Advanced Techniques
    Replies: 1
    Last Post: February 23rd, 01:25 PM
  2. How u convert this cfquery to TSQL?
    By roofusthedoofus in forum Coldfusion Database Access
    Replies: 2
    Last Post: February 22nd, 09:13 PM
  3. Is it possible to do this using TSQL instead of cursor ??
    By Ricky in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 7th, 05:50 PM
  4. IIF/Format equivalent in TSQL
    By andi in forum Microsoft SQL / MS SQL Server
    Replies: 5
    Last Post: July 1st, 05:11 PM
  5. Please improve this TSQL algorithm ..
    By Krist Lioe in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: June 30th, 08:18 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139