Results 1 to 14 of 14
  1. #1
    Join Date
    Aug 2003
    Posts
    34

    Unanswered: Execute Statements in Order

    Dear friends,

    I am using query analyzer to build a database,
    I want to do certain command in order, that is: not to execute the next statement until the previous one has been finish execution.
    What is the command used for this purpose

    Thanks for your valuable help

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    GO

    The message I have entered is too short
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You don't even need the GO command. Sequential statements in a script execute sequentially anyway.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jun 2003
    Posts
    269
    Quote Originally Posted by blindman
    You don't even need the GO command. Sequential statements in a script execute sequentially anyway.
    unless there is no 'goto' statement
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  5. #5
    Join Date
    Apr 2006
    Posts
    6
    You need a GO ....If you have sequential steps SL server will open multiple threads and execute it independent of each other.....

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by tariqdba
    You need a GO ....If you have sequential steps SL server will open multiple threads and execute it independent of each other.....
    I've never seen separate statements in a SQL batch executed out of order. I don't believe that is possible.

    You can use IF...THEN...ELSE, WHILE, and RETURN to control flow, and there is still GOTO (which is rarely used), but otherwise the individual (atomic) SQL statements are executed in the order that they are specified. Within a given statement like a SELECT, different clauses can execute unpredictably (for example the JOINs can materialize in whatever order the database engine finds convenient), but the individual SQL statements are always executed in sequence as directed by the flow of control statements.

    -PatP

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by tariqdba
    You need a GO ....If you have sequential steps SL server will open multiple threads and execute it independent of each other.....
    Absolultely not. TSQL is a procedural language.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Aug 2003
    Posts
    34
    From originator,

    Thansk for all, but,
    I believe that the statments will start excute sequentially, but
    for example if i have 3 statments, the first needs 4 minites to finish execute
    the second and third needs only one second,
    in this case the server will start excute the first statment, then the second ( before the first finishes) , then the third

    why i think like this,

    I have around 30 statments to import data from MS Access into MS SQL
    when i excute the statments by marking command by command , then pressing F5. It works fine,
    but when i excutes all at the same time , it will give errors...

    I tried GO but still giving errors

    Thanks again for effort.
    Last edited by ridwan; 04-15-06 at 01:40.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Can you post your script? I'm not sure what problem(s) you are finding, but I can guarantee you that the statements will be processed one at a time, in the order that they appear in the script (unless you have statements that explicitly change the flow of control such as IF...THEN...ELSE).

    -PatP

  10. #10
    Join Date
    Aug 2003
    Posts
    34
    The Script is as follows:

    select Schools

    truncate table [Log] ----
    truncate table Course ----
    truncate table Exam ----
    truncate table Exam4 ----
    truncate table ExamDef ----
    truncate table Payment ----
    truncate table Permit ----
    truncate table Prohibit ----
    truncate table SecTopicSub2 ----
    truncate table Student ----
    truncate table Groups ----
    truncate table DailyTransaction ----
    truncate table reGrouping ----
    truncate table rePayment ----
    truncate table SalesVoucher ----

    truncate table AccRestrict -- should keep some users
    truncate table SecTopicSub -- should keep some users
    truncate table SPass -- should keep some users
    truncate table City
    truncate table Nationality
    truncate table Sales
    truncate table CourseT
    truncate table Classify
    truncate table ClassRoom
    truncate table Period
    truncate table PermitNo
    truncate table Reference
    truncate table Remarks
    truncate table [Static]
    truncate table StaticB
    truncate table Stations
    truncate table Trade
    truncate table SS1_Locked_Records


    go

    INSERT INTO AccRestrict (Code,User1,access) SELECT * FROM
    OPENROWSET('msdasql', 'DRIVER=Microsoft Access Driver (*.mdb);pwd=avonlea;DBQ=c:\temp\SchoolR.mdb',
    'SELECT Code,User1,access from AccRestrict') as aa


    INSERT INTO City (Code,Desc1) SELECT * FROM
    OPENROWSET('msdasql', 'DRIVER=Microsoft Access Driver (*.mdb);pwd=avonlea;DBQ=c:\temp\SchoolR.mdb',
    'SELECT Code,Desc1 from City') as aa


    INSERT INTO Classify (Class,Desc1) SELECT * FROM
    OPENROWSET('msdasql', 'DRIVER=Microsoft Access Driver (*.mdb);pwd=avonlea;DBQ=c:\temp\SchoolR.mdb',
    'SELECT Class, Desc1 from Classify ') as aa

    go

    INSERT INTO ClassRoom (ClassNo,Seats,Desc1) SELECT * FROM
    OPENROWSET('msdasql', 'DRIVER=Microsoft Access Driver (*.mdb);pwd=avonlea;DBQ=c:\temp\SchoolR.mdb',
    'SELECT ClassNo,Seats,Desc1 from ClassRoom ') as aa


    INSERT INTO Course (CourseID,CourseT, CourseNo, CName, StartG,StartH, EndG ,EndH ,
    Period, FromTime, ToTime,Open1,
    EnterResult, Periods, Max1, Current1, Days, AllowAbs, Amount, Station, User1,
    School, ClassRoom,Limit1,Limit2,Limit3,Limit4,Limit5,Regis ter1,Register2,Register3,Register4,Register5,
    DateSG1,DateSG2,DateSG3,DateSG4,DateSG7,DateSH1,Da teSH2,DateSH3,DateSH4,DateSH7)
    SELECT * FROM
    OPENROWSET('msdasql', 'DRIVER=Microsoft Access Driver (*.mdb);pwd=avonlea;DBQ=c:\temp\SchoolR.mdb',
    'SELECT CourseID,CourseT, CourseNo, CName, StartG,Str(StartH), EndG ,Str(EndH) ,Period, FromTime, ToTime,Open1,
    EnterResult, Periods, Max1, Current1, Days, AllowAbs, Amount, Station, User1,
    School, ClassRoom,Limit1,Limit2,Limit3,Limit4,Limit5,Regis ter1,Register2,Register3,Register4,Register5,
    DateSG1,DateSG2,DateSG3,DateSG4,DateSG7,Str(DateSH 1),Str(DateSH2), Str(DateSH3),Str(DateSH4), Str(DateSH7)
    from Course') as aa

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

    INSERT INTO CourseT (CourseT, CName,Amount,Type1, Type2, PrintForm, Active, Remarks,
    Days,Periods, AllowAbs, Class, ExamSort, StatSort, StatSortB, User1, StudList, Min_Age,
    AllowDaysDistribution ) SELECT * FROM
    OPENROWSET('msdasql', 'DRIVER=Microsoft Access Driver (*.mdb);pwd=avonlea;DBQ=c:\temp\SchoolR.mdb',
    'SELECT CourseT, CName,Amount,Type1, Type2, PrintForm, Active, Remarks,
    Days,Periods, AllowAbs, Class, ExamSort, StatSort, StatSortB, User1, StudList, Min_Age,
    AllowDaysDistribution from CourseT ') as aa
    go
    ------------------------------------------------------------
    --truncate table exam
    INSERT INTO Exam (StudID,Course,ExNo, ReExam, DateG, DateH ,Result, Result1, Result2, Result3,
    Remarks, Station, User1, School, Sno)
    SELECT * FROM
    OPENROWSET('msdasql', 'DRIVER=Microsoft Access Driver (*.mdb);pwd=avonlea;DBQ=c:\temp\SchoolR.mdb',
    'Select StudID,Course,ExNo, ReExam, iif(DateG>#01/01/1990# and DateG<#01/01/2010#, DateG ,null) as DateG1 ,
    left(str(DateH),10) as DateH1 ,Result, Result1, Result2, Result3,
    Remarks, Station, User1, School, Sno from Exam where SNo <> 33759085') as aa
    go
    -- select Top 20000 * from exam
    Update Exam set dateh = '0'+DateH where substring(DateH,2,1)='/'
    Update Exam set dateh = left(DateH,3)+'0'+substring(DateH,4,6) where substring(DateH,5,1)='/'
    update exam set DateH = Substring(DateH,4,2) + '/' + left(dateh,2) + '/' + substring(dateH,7,4) where substring(DateH,4,2) > '12'

    -- where SNo <> 33759085 ') as aa -- for Jizan only
    ------------------------------------------------------------

    set IDENTITY_INSERT Exam4 On
    go

    INSERT INTO Exam4 ([ID], StudID, Course, ExNo, DateG, DateH, ExpiryG, Expire, PermitID, Remarks, Address, Tel)
    SELECT * FROM
    OPENROWSET('msdasql', 'DRIVER=Microsoft Access Driver (*.mdb);pwd=avonlea;DBQ=c:\temp\SchoolR.mdb',
    'select ID, StudID, Course, ExNo, DateG, Str(DateH), ExpiryG, Expire, PermitID, Remarks, Address, Tel
    from exam4') as aa
    set IDENTITY_INSERT Exam4 off
    ------------------------------------------------------------
    go

    INSERT INTO ExamDef (ExamNo,DateG, DateH, MaxNorm, MaxFail, CurrNorm, CurrFail, Status)
    SELECT * FROM
    OPENROWSET('msdasql', 'DRIVER=Microsoft Access Driver (*.mdb);pwd=avonlea;DBQ=c:\temp\SchoolR.mdb',
    'select ExamNo,iif(DateG<#01/01/1900#,#01/01/1900#, DateG), Str(DateH), MaxNorm, MaxFail,
    CurrNorm, CurrFail, Status from ExamDef') as aa

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

    INSERT INTO Nationality (Code,Desc1) SELECT * FROM
    OPENROWSET('msdasql', 'DRIVER=Microsoft Access Driver (*.mdb);pwd=avonlea;DBQ=c:\temp\SchoolR.mdb',
    'SELECT Code,Desc1 from Nationality') as aa

    go

    --------------------------------------------------------------
    set IDENTITY_INSERT Payment On
    --------------------------------------------------------------

    INSERT INTO Payment (PayNo, PayDateG, PayDateH,PayType,StudID,Course ,Amount,Result, Absence,
    Group1, Printed ,DialogPrinted, OldPay,
    OldSchool, WithDraw ,Station ,User1,Copied, School, Sno,CStartDay,List)
    SELECT * FROM
    OPENROWSET('msdasql', 'DRIVER=Microsoft Access Driver (*.mdb);pwd=avonlea;DBQ=c:\temp\SchoolR.mdb',
    'select PayNo, IIF(PayDateG<#01/01/1900#,#01/01/1900#,PayDateG) as PayDateG1,
    left(Str(PayDateH),10) as PayDateH1,PayType,StudID,Course ,Amount,Result,
    Absence, Group1, Printed ,DialogPrinted, OldPay,
    OldSchool, WithDraw ,Station ,User1,Copied, School, Sno, CStartDay,List
    FROM [payment]') AS aa where Len(PayDateH1)<=10 -- this last where is for Jizan = keep this since no need to such record (empty)

    --------------------------------------------------------------
    set IDENTITY_INSERT Payment Off
    go

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

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


    INSERT INTO SecTopicSub SELECT * FROM
    OPENROWSET('msdasql', 'DRIVER=Microsoft Access Driver (*.mdb);pwd=avonlea;DBQ=c:\temp\SchoolR.mdb',
    'select * FROM SecTopicSub ') AS aa

    INSERT INTO SecTopicSub2 SELECT * FROM
    OPENROWSET('msdasql', 'DRIVER=Microsoft Access Driver (*.mdb);pwd=avonlea;DBQ=c:\temp\SchoolR.mdb',
    'select * FROM SecTopicSub2 ') AS aa


    INSERT INTO Spass (user1,UserName, access, [password], lastpchanged,
    logged, [time], AutoList, IDFirst, AddMode, CourseFilter1, DialogPrint )
    SELECT * FROM
    OPENROWSET('msdasql', 'DRIVER=Microsoft Access Driver (*.mdb);pwd=avonlea;DBQ=c:\temp\SchoolR.mdb',
    'select user1,UserName, access, [password], lastpchanged,
    logged, [time], AutoList, IDFirst, AddMode, CourseFilter1, DialogPrint FROM Spass ') AS aa

    go

    INSERT INTO Static SELECT * FROM
    OPENROWSET('msdasql', 'DRIVER=Microsoft Access Driver (*.mdb);pwd=avonlea;DBQ=c:\temp\SchoolR.mdb',
    'select * FROM Static ') AS aa

    INSERT INTO StaticB SELECT * FROM
    OPENROWSET('msdasql', 'DRIVER=Microsoft Access Driver (*.mdb);pwd=avonlea;DBQ=c:\temp\SchoolR.mdb',
    'select * FROM StaticB ') AS aa

    INSERT INTO Stations (Code,Desc1,PrinterTop1) SELECT * FROM
    OPENROWSET('msdasql', 'DRIVER=Microsoft Access Driver (*.mdb);pwd=avonlea;DBQ=c:\temp\SchoolR.mdb',
    'select Code,Desc1, iif(PrinterTop1<0,0,PrinterTop1) FROM Stations ') AS aa

    ------------------------------------------------------------
    --select * from stations
    go

    INSERT INTO groups SELECT * FROM
    OPENROWSET('msdasql', 'DRIVER=Microsoft Access Driver (*.mdb);pwd=avonlea;DBQ=c:\temp\SchoolR.mdb',
    'select * FROM groups') AS aa

    go

    set IDENTITY_INSERT DailyTransaction On

    INSERT INTO DailyTransaction (SNo,[Date],Amount,Posted,Batch) SELECT * FROM
    OPENROWSET('msdasql', 'DRIVER=Microsoft Access Driver (*.mdb);pwd=avonlea;DBQ=c:\temp\SchoolR.mdb',
    'select * FROM DailyTransaction') AS aa

    set IDENTITY_INSERT DailyTransaction Off

    go

    INSERT INTO reGrouping SELECT * FROM
    OPENROWSET('msdasql', 'DRIVER=Microsoft Access Driver (*.mdb);pwd=avonlea;DBQ=c:\temp\SchoolR.mdb',
    'select * FROM reGrouping') AS aa


    go

    set IDENTITY_INSERT rePayment On

    INSERT INTO rePayment (NewPayNo, PayDateG,PayDateH,StudID,Amount,Station,User1, Course,CName,
    PayType, PayNo,OldPay,OldSchool) SELECT * FROM
    OPENROWSET('msdasql', 'DRIVER=Microsoft Access Driver (*.mdb);pwd=avonlea;DBQ=c:\temp\SchoolR.mdb',
    'select NewPayNo, PayDateG,Str(PayDateH),StudID,Amount,Station,User1 , Course,CName,
    PayType, PayNo,OldPay,OldSchool FROM rePayment') AS aa

    set IDENTITY_INSERT rePayment Off

    Go

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    First observation, TRUNCATE TABLE is a complete wipe of the table... Nothing is ever left in a truncated table.

    What kind of errors are you getting, and where are you getting them? The rest of this script looks Ok at least from a simple look.

    -PatP

  12. #12
    Join Date
    Aug 2003
    Posts
    34
    after i implement GO, there was one problem, but i correct and the transaction works fine ,
    thanks for help.

    Ridwan

  13. #13
    Join Date
    Aug 2003
    Posts
    34
    how to close this Thread?

  14. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm glad that you were able to find and fix your problem.

    If implementing the GO between statements makes you happy, that's good, but it was definitely not part of your solution. While the use of the GO statements would not hurt anything, they would not help either, so removing those GO statements from your corrected script wouldn't change anything. There are a few SQL statements that must be the first or only statement in a batch (so they require the use of GO), but none of them are used in the script that you posted.

    We don't normally close threads here at DBForums. It can be done, but it is pointless in nearly all cases.

    -PatP

Posting Permissions

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