写了个程序,请参考: procedure TForm1.Button1Click(Sender: TObject); var Astr: array [0..2] of string; MySQL: string; str: string; i: integer; begin MySQL := 'SELECT * FROM TTT'; with ADOQuery1 do begin Close; SQL.Clear; SQL.Text := MySQL; Open; while not eof do begin str := trim(FieldByName('tname').AsString); for i := 0 to 2 do begin Astr[i] := copy(str,1,pos(' ',str)-1); str := copy(str,pos(' ',str)+1,length(str)-pos(' ',str)); if pos(' ',str) = 0 then begin Astr[i+1] := str; Break; end; end; MySQL := 'INSERT INTO TTTT VALUES('''+Astr[0]+''','''+Astr[1]+''','''+Astr[2]+''')'; with ADOQuery2 do begin Close; SQL.Clear; SQL.Text := MySQL; try ExecSQL; except Application.MessageBox('sdf','fffff',mb_ok+mb_iconstop); Abort end; end; Next; end; Application.MessageBox('转换成功','恭喜',mb_ok+mb_iconinformation); end; end;
用存储过程省事: if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CalCul]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[CalCul] gocreate Procedure [dbo].[CalCul] as begin declare @Para1 nvarchar(20),@Para2 nvarchar(20),@Para3 nvarchar(20) declare @TempStr nvarchar(1000) declare @StartPos int,@EndPos int declare CurTable1 cursor for select prd_no from 表1 fetch cursor into @TempStr while @@fetch_status<>-1 begin set @StartPos=charindex(' ',@TempStr) set @EndPos=charindex(' ',@TempStr,@startPos+1) set @Para1=substring(@TempStr,1,@StartPos-1) set @Para2=substring(@TempStr,@StartPos+1,@EndPos-@StartPos-1) set @Para3=substring(@TempStr,@EndPos+1,len(@TempStr)-@EndPos) insert into 表2 values(@Para1,@Para2,@Para3) fetch cursor into @TempStr endend go
2把值付给变量
3用substr取值(按空格)
4update新表
则利用TSTringList 将Delimitle设为#32
procedure TForm1.Button1Click(Sender: TObject);
var
Astr: array [0..2] of string;
MySQL: string;
str: string;
i: integer;
begin
MySQL := 'SELECT * FROM TTT';
with ADOQuery1 do
begin
Close;
SQL.Clear;
SQL.Text := MySQL;
Open;
while not eof do
begin
str := trim(FieldByName('tname').AsString);
for i := 0 to 2 do
begin
Astr[i] := copy(str,1,pos(' ',str)-1);
str := copy(str,pos(' ',str)+1,length(str)-pos(' ',str));
if pos(' ',str) = 0 then
begin
Astr[i+1] := str;
Break;
end;
end;
MySQL := 'INSERT INTO TTTT VALUES('''+Astr[0]+''','''+Astr[1]+''','''+Astr[2]+''')';
with ADOQuery2 do
begin
Close;
SQL.Clear;
SQL.Text := MySQL;
try
ExecSQL;
except
Application.MessageBox('sdf','fffff',mb_ok+mb_iconstop);
Abort
end;
end;
Next;
end;
Application.MessageBox('转换成功','恭喜',mb_ok+mb_iconinformation);
end;
end;
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CalCul]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CalCul]
gocreate Procedure [dbo].[CalCul]
as
begin declare @Para1 nvarchar(20),@Para2 nvarchar(20),@Para3 nvarchar(20)
declare @TempStr nvarchar(1000)
declare @StartPos int,@EndPos int
declare CurTable1 cursor for
select prd_no from 表1
fetch cursor into @TempStr
while @@fetch_status<>-1
begin
set @StartPos=charindex(' ',@TempStr)
set @EndPos=charindex(' ',@TempStr,@startPos+1)
set @Para1=substring(@TempStr,1,@StartPos-1)
set @Para2=substring(@TempStr,@StartPos+1,@EndPos-@StartPos-1)
set @Para3=substring(@TempStr,@EndPos+1,len(@TempStr)-@EndPos)
insert into 表2 values(@Para1,@Para2,@Para3)
fetch cursor into @TempStr
endend
go