直接用SQL语句把DBF导入SQLServer

在SQLServer中执行
SELECT * into bmk
FROM OpenDataSource( ’Microsoft.Jet.OLEDB.4.0’,
’Data Source=”e:share”;User ID=Admin;Password=;Extended properties=dBase 5.0’)…bmk
这样就可以把e:share中的bmk.dbf表导入到Sqlserver中，
速度是最快的

—————————-

DBF —-> SQL SERVER
drop table dbf
SELECT * into dbf
FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0',
'Data Source="E:SuperDataSuperDate_停电预演rundataEngine东海工程合并";User ID=;Password=;Extended properties=dBase 5.0')…dbf
go

——————————————————成功

EXCEL —-> SQL SERVER

SELECT * INTO YourTableName FROM
OPENROWSET('MSDASQL.1', 'driver=Microsoft Excel Driver (*.xls);DBQ=D:ExcelBook2.xls',
'select * from [sheet1$]')

————————————————成功

Access —->SQL Server
INSERT INTO access
SELECT * into access
FROM opendatasource( 'Microsoft.Jet.OLEDB.4.0','Data Source="D:MapX_Web.mdb";Jet OLEDB:Database Password=')…Layer_StyleIndex
———————————————-成功

function TForm1.Boolean_Excel_To_SQLServer_UsingStringList(p_Str_MachineName : String;
p_Str_DataBaseName : String;
p_Str_UserName : String;
p_Str_Password : String;
p_StringList_FileNameAndPath : TStringList;
p_ProgressBar : TProgressBar
) : Boolean;
var // false : 操作失败
m_ADOConnection : TADOConnection;
m_Str_FileNameAndPath : String;
m_Str_FileName : String;
m_Str_PathName : String;
m_StrSQL : String;
m_StrSQL1 : String;
m_Integer_ItemPos : Integer;
begin
m_ADOConnection := TADOConnection.Create(nil);
m_ADOConnection.ConnectionString :=
'Provider=SQLOLEDB.1;' +
'Password=' + p_Str_Password + ';' +
'Persist Security Info=True;' +
'User ID=' + p_Str_UserName + ';' +
'Initial Catalog=' + p_Str_DataBaseName + ';' +
'Data Source=' + p_Str_MachineName;
m_ADOConnection.LoginPrompt := false;
try
//连接数据库服务器
m_ADOConnection.Connected := true;
except
ShowMessage('Boolean_DBF_To_SQLServer()::数据库连接出错！');
Result := false;
Exit;
end;

if p_ProgressBar <> nil then
begin
p_ProgressBar.Min := 0;
p_ProgressBar.Max := p_StringList_FileNameAndPath.Count;
end;
for m_Integer_ItemPos := 1 to p_StringList_FileNameAndPath.Count do
begin
m_Str_FileNameAndPath := p_StringList_FileNameAndPath.Strings[m_Integer_ItemPos – 1];
m_Str_FileName := Self.String_GetFileNameFromOneFullFileScript(m_Str_FileNameAndPath);
m_Str_FileName := Self.String_DeleteExtNameForOneFileName(m_Str_FileName);
m_Str_PathName := Self.String_GetFilePathFromOneFullFileScript(m_Str_FileNameAndPath);
m_StrSQL := 'drop table ' + m_Str_FileName;
try
//删除SQLSERVER中对应的原有表（如果有）
m_ADOConnection.Execute(m_StrSQL);
except
end;
{
m_StrSQL := 'SELECT * INTO ' + m_Str_FileName +
' FROM OpenDataSource(''Microsoft.Jet.OLEDB.4.0'', ' +
'''Data Source="' + m_Str_PathName + '";User ID=;Password=;Extended properties=dBase 5.0'')…' + m_Str_FileName;
}
{
SELECT * INTO YourTableName FROM
OPENROWSET('MSDASQL.1', 'driver=Microsoft Excel Driver (*.xls);DBQ=D:ExcelBook2.xls',
'select * from [sheet1$]')
}
m_StrSQL := 'SELECT * INTO ' + m_Str_FileName + ' FROM ' +
'OPENROWSET(''MSDASQL.1'', ''driver=Microsoft Excel Driver (*.xls);DBQ=' + m_Str_PathName + m_Str_FileName + '.xls'',' +
'''select * from [sheet1$]'')';
{
m_StrSQL1 := 'SELECT * INTO Book2 FROM ' +
'OPENROWSET(''MSDASQL.1'', ''driver=Microsoft Excel Driver (*.xls);DBQ=D:ExcelBook2.xls'',' +
'''select * from [sheet1$]'')';
}
try
m_ADOConnection.Execute(m_StrSQL);
except
ShowMessage(m_Str_FileName + ' 表 上传出错！');
Result := false;
Exit
end;
if p_ProgressBar <> nil then
begin
p_ProgressBar.Position := m_Integer_ItemPos;
end;
end;

Result := true;
end;

function TForm1.Boolean_DBF_To_SQLServer_UsingStringList(p_Str_MachineName : String; //机器名
p_Str_DataBaseName : String; //数据库名
p_Str_UserName : String; //用户名
p_Str_Password : String; //用户密码
p_StringList_FileNameAndPath : TStringList; //文件名和路径列表
p_ProgressBar : TProgressBar //进度条
) : Boolean; // true : 操作成功
var // false : 操作失败
m_ADOConnection : TADOConnection;
m_Str_FileNameAndPath : String;
m_Str_FileName : String;
m_Str_PathName : String;
m_StrSQL : String;
m_Integer_ItemPos : Integer;
begin
m_ADOConnection := TADOConnection.Create(nil);
m_ADOConnection.ConnectionString :=
'Provider=SQLOLEDB.1;' +
'Password=' + p_Str_Password + ';' +
'Persist Security Info=True;' +
'User ID=' + p_Str_UserName + ';' +
'Initial Catalog=' + p_Str_DataBaseName + ';' +
'Data Source=' + p_Str_MachineName;
m_ADOConnection.LoginPrompt := false;
try
//连接数据库服务器
m_ADOConnection.Connected := true;
except
ShowMessage('Boolean_DBF_To_SQLServer()::数据库连接出错！');
Result := false;
Exit;
end;

if p_ProgressBar <> nil then
begin
p_ProgressBar.Min := 0;
p_ProgressBar.Max := p_StringList_FileNameAndPath.Count;
end;
for m_Integer_ItemPos := 1 to p_StringList_FileNameAndPath.Count do
begin
m_Str_FileNameAndPath := p_StringList_FileNameAndPath.Strings[m_Integer_ItemPos – 1];
m_Str_FileName := Self.String_GetFileNameFromOneFullFileScript(m_Str_FileNameAndPath);
m_Str_FileName := Self.String_DeleteExtNameForOneFileName(m_Str_FileName);
m_Str_PathName := Self.String_GetFilePathFromOneFullFileScript(m_Str_FileNameAndPath);
m_StrSQL := 'drop table ' + m_Str_FileName;
try
//删除SQLSERVER中对应的原有表（如果有）
m_ADOConnection.Execute(m_StrSQL);
except
end;
m_StrSQL := 'SELECT * INTO ' + m_Str_FileName +
' FROM OpenDataSource(''Microsoft.Jet.OLEDB.4.0'', ' +
'''Data Source="' + m_Str_PathName + '";User ID=;Password=;Extended properties=dBase 5.0'')…' + m_Str_FileName;
try
m_ADOConnection.Execute(m_StrSQL);
except
ShowMessage(m_Str_FileName + ' 表 上传出错！');
Result := false;
Exit
end;
if p_ProgressBar <> nil then
begin
p_ProgressBar.Position := m_Integer_ItemPos;
end;
end;

Result := true;
end;

打赏作者