1楼能具体一点吗?譬如: 视图1: CREATE VIEW dbo.SCGL_GYSB_V AS SELECT dbo.SCGL_GYSBZB.CJCODE, dbo.SCGL_GYSBZB.CJMC, dbo.SCGL_GYSBZB.GYSBID, dbo.SCGL_GYSBZB.YGCODE, dbo.SCGL_GYSBZB.YGMC, dbo.SCGL_GYSBZB.SBRQ, dbo.SCGL_GYSBZB.ZDYGCODE, dbo.SCGL_GYSBZB.ZDYGMC, FROM dbo.SCGL_GYSBMXB INNER JOIN dbo.SCGL_GYSBZB ON dbo.SCGL_GYSBMXB.GYSBID = dbo.SCGL_GYSBZB.GYSBID 视图2: CREATE VIEW dbo.CGGL_XJD_V AS SELECT dbo.CGGL_XJDZB.XJDID, dbo.CGGL_XJDZB.GYSCODE, dbo.CGGL_XJDZB.GYSMC, dbo.CGGL_XJDZB.YSFSCODE, dbo.CGGL_XJDZB.YSFSMC, dbo.CGGL_XJDZB.CGLB, dbo.CGGL_XJDZB.YWYCODE, dbo.CGGL_XJDZB.YWYMC, dbo.CGGL_XJDZB.JSFSCODE, dbo.CGGL_XJDZB.JSFSMC, dbo.CGGL_XJDZB.FKFSCODE, FROM dbo.CGGL_XJDZB INNER JOIN dbo.CGGL_XJDMXB ON dbo.CGGL_XJDZB.XJDID = dbo.CGGL_XJDMXB.XJDID怎么同时用一个SQL语句建????
//1楼已经说出思路了,LZ应该思考一下,不应该只追求现成的!:)var FString:TStrings; begin FString:=TStringList.Create; try FString.Add('EXEC (''CREATE VIEW C AS SELECT ORDERID FROM ORDERS'')'); FString.Add('EXEC (''CREATE VIEW B AS SELECT ORDERID FROM ORDERS'')'); With ADOQuery1 do begin Close; Sql.Clear; Sql:=FString; ExecSQL; end; finally FString.Free; end; end;
楼上正解 var sStr : string; begin sStr := 'Exec('' CREATE VIEW dbo.SCGL_GYSB_V AS' + sStr := sStr + ' SELECT dbo.SCGL_GYSBZB.CJCODE, dbo.SCGL_GYSBZB.CJMC,dbo.SCGL_GYSBZB.GYSBID, dbo.SCGL_GYSBZB.YGCODE,' + sStr := sStr + ' dbo.SCGL_GYSBZB.YGMC, dbo.SCGL_GYSBZB.SBRQ,dbo.SCGL_GYSBZB.ZDYGCODE, dbo.SCGL_GYSBZB.ZDYGMC,'+ sStr := sStr + ' FROM dbo.SCGL_GYSBMXB INNER JOIN dbo.SCGL_GYSBZB ON dbo.SCGL_GYSBMXB.GYSBID = dbo.SCGL_GYSBZB.GYSBID '')'; With ADOQuery1 do begin Close; Sql.Clear; Sql.text := sStr; end; end;
而LZ希望将一批语句直接提交到DBMS中,而不是一条一条执行,故需要从动态拼组SQL这边入手!
即将每个Create View变为Exec('Create View ....'),把每一条放到一个StringList的Item中,这样就可以实现批量处理SQL了,从而避免了不必要地总与DBMS交互!
视图1:
CREATE VIEW dbo.SCGL_GYSB_V
AS
SELECT dbo.SCGL_GYSBZB.CJCODE, dbo.SCGL_GYSBZB.CJMC,
dbo.SCGL_GYSBZB.GYSBID, dbo.SCGL_GYSBZB.YGCODE,
dbo.SCGL_GYSBZB.YGMC, dbo.SCGL_GYSBZB.SBRQ,
dbo.SCGL_GYSBZB.ZDYGCODE, dbo.SCGL_GYSBZB.ZDYGMC,
FROM dbo.SCGL_GYSBMXB INNER JOIN
dbo.SCGL_GYSBZB ON dbo.SCGL_GYSBMXB.GYSBID = dbo.SCGL_GYSBZB.GYSBID
视图2:
CREATE VIEW dbo.CGGL_XJD_V
AS
SELECT dbo.CGGL_XJDZB.XJDID, dbo.CGGL_XJDZB.GYSCODE,
dbo.CGGL_XJDZB.GYSMC, dbo.CGGL_XJDZB.YSFSCODE,
dbo.CGGL_XJDZB.YSFSMC, dbo.CGGL_XJDZB.CGLB, dbo.CGGL_XJDZB.YWYCODE,
dbo.CGGL_XJDZB.YWYMC, dbo.CGGL_XJDZB.JSFSCODE,
dbo.CGGL_XJDZB.JSFSMC, dbo.CGGL_XJDZB.FKFSCODE,
FROM dbo.CGGL_XJDZB INNER JOIN
dbo.CGGL_XJDMXB ON dbo.CGGL_XJDZB.XJDID = dbo.CGGL_XJDMXB.XJDID怎么同时用一个SQL语句建????
FString:TStrings;
begin
FString:=TStringList.Create;
try
FString.Add('EXEC (''CREATE VIEW C AS SELECT ORDERID FROM ORDERS'')');
FString.Add('EXEC (''CREATE VIEW B AS SELECT ORDERID FROM ORDERS'')');
With ADOQuery1 do
begin
Close;
Sql.Clear;
Sql:=FString;
ExecSQL;
end;
finally
FString.Free;
end;
end;
var
sStr : string;
begin
sStr := 'Exec('' CREATE VIEW dbo.SCGL_GYSB_V AS' +
sStr := sStr + ' SELECT dbo.SCGL_GYSBZB.CJCODE, dbo.SCGL_GYSBZB.CJMC,dbo.SCGL_GYSBZB.GYSBID, dbo.SCGL_GYSBZB.YGCODE,' +
sStr := sStr + ' dbo.SCGL_GYSBZB.YGMC, dbo.SCGL_GYSBZB.SBRQ,dbo.SCGL_GYSBZB.ZDYGCODE, dbo.SCGL_GYSBZB.ZDYGMC,'+
sStr := sStr + ' FROM dbo.SCGL_GYSBMXB INNER JOIN dbo.SCGL_GYSBZB ON dbo.SCGL_GYSBMXB.GYSBID = dbo.SCGL_GYSBZB.GYSBID '')'; With ADOQuery1 do
begin
Close;
Sql.Clear;
Sql.text := sStr;
end;
end;
不是一样要写许多SQL语句在delphi程序中,我想要的是读MSSQL脚本.
MSSQL脚本里有许多视图,那样一条一条的写进去,太耗时间了.
with adoquery1 do
begin
Close;
sql.Clear;
SQL.LoadFromFile('sql.txt'); // 存放的SQL脚本的位置
execsql;
end;最好能这样实现,那样就方便多了!!请教高手指点,sql.txt 全是SQL视图脚本.运行程序会出现 " Create view 必须是批查询中的第一条语句!" 错误.要怎么才能实现呢?
使用osql.exe来直接执行外部脚本不就行了!
这样写吗?
还是有错~~