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 helpGO
The message I have entered is too short|||You don't even need the GO command. Sequential statements in a script execute sequentially anyway.|||You don't even need the GO command. Sequential statements in a script execute sequentially anyway.
unless there is no 'goto' statement|||You need a GO ...If you have sequential steps SL server will open multiple threads and execute it independent of each other....|||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|||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.|||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.|||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|||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|||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|||after i implement GO, there was one problem, but i correct and the transaction works fine ,
thanks for help.
Ridwan|||how to close this Thread?|||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
