SQL SERVER 数据导出导入(100分)

  • 主题发起人 southskylg
  • 开始时间
S

southskylg

Unregistered / Unconfirmed
GUEST, unregistred user!
如何把数据导出到一个ACCESS 库中,使用ADO连接,并且是大数据量批次导出,
DELPHI控件batchmove好像只能BDE连接哦
 
好像sql有一个工具压
如果要用程序的好像比较麻烦,如果自己用代码写的化,效率太第了
 
SQL server中有一个工具DTS
 
把导出导入做成dts包,在sql的存贮过程序中执行
 
可以导入SQL SERVER DTS的Type Library,调用这个COM组件来实现数据导入导出
 
可以自己写代码用Tadoconnection,Tadoquery
 
主要是DTS不具有普遍性,它只是一个工具而已,寻求类似BATCHMOVE的ADO连接模式?
 
用SQL SERVER的导出数据的功能,很方便的把数据导到Access中去
 
以下是一段VB代码,你把它转成DELPHI的就可以在DELPHI中调用了:
'****************************************************************
'Microsoft SQL Server 2000
'Visual Basic file generated for DTS Package
'File Name: D:/Documents and Settings/yangyaohua/My Documents/新建包.bas
'Package Name: 新建包
'Package Description: DTS 包描述
'Generated Date: 2002-12-5
'Generated Time: 9:14:18
'****************************************************************

Option Explicit
Public goPackageOld As New DTS.Package
Public goPackage As DTS.Package2
Private Sub Main()
set goPackage = goPackageOld

goPackage.Name = "新建包"
goPackage.Description = "DTS 包描述"
goPackage.WriteCompletionStatusToNTEventLog = False
goPackage.FailOnError = False
goPackage.PackagePriorityClass = 2
goPackage.MaxConcurrentSteps = 4
goPackage.LineageOptions = 0
goPackage.UseTransaction = True
goPackage.TransactionIsolationLevel = 4096
goPackage.AutoCommitTransaction = True
goPackage.RepositoryMetadataOptions = 0
goPackage.UseOLEDBServiceComponents = True
goPackage.LogToSQLServer = False
goPackage.LogServerFlags = 0
goPackage.FailPackageOnLogFailure = False
goPackage.ExplicitGlobalVariables = False
goPackage.PackageType = 0


Dim oConnProperty As DTS.OleDBProperty

'---------------------------------------------------------------------------
' create package connection information
'---------------------------------------------------------------------------

Dim oConnection as DTS.Connection2

'------------- a new connection defined below.
'For security purposes, the password is never scripted

Set oConnection = goPackage.Connections.New("SQLOLEDB")

oConnection.ConnectionProperties("Persist Security Info") = True
oConnection.ConnectionProperties("User ID") = "SA"
oConnection.ConnectionProperties("Initial Catalog") = "ZCSoftMRP"
oConnection.ConnectionProperties("Data Source") = "SERVER"
oConnection.ConnectionProperties("Application Name") = "DTS 导入/导出向导"

oConnection.Name = "连接1"
oConnection.ID = 1
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "SERVER"
oConnection.UserID = "SA"
oConnection.ConnectionTimeout = 60
oConnection.Catalog = "ZCSoftMRP"
oConnection.UseTrustedConnection = False
oConnection.UseDSL = False

'If you have a password for this connection, please uncomment and add your password below.
'oConnection.Password = "<put the password here>"

goPackage.Connections.Add oConnection
Set oConnection = Nothing

'------------- a new connection defined below.
'For security purposes, the password is never scripted

Set oConnection = goPackage.Connections.New("Microsoft.Jet.OLEDB.4.0")

oConnection.ConnectionProperties("Data Source") = "D:/Documents and Settings/yangyaohua/My Documents/test.mdb"
oConnection.ConnectionProperties("Mode") = 3

oConnection.Name = "连接2"
oConnection.ID = 2
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "D:/Documents and Settings/yangyaohua/My Documents/test.mdb"
oConnection.ConnectionTimeout = 60
oConnection.UseTrustedConnection = False
oConnection.UseDSL = False

'If you have a password for this connection, please uncomment and add your password below.
'oConnection.Password = "<put the password here>"

goPackage.Connections.Add oConnection
Set oConnection = Nothing

'---------------------------------------------------------------------------
' create package steps information
'---------------------------------------------------------------------------

Dim oStep as DTS.Step2
Dim oPrecConstraint as DTS.PrecedenceConstraint

'------------- a new step defined below

Set oStep = goPackage.Steps.New

oStep.Name = "创建表 Roles 步骤"
oStep.Description = "创建表 Roles 步骤"
oStep.ExecutionStatus = 1
oStep.TaskName = "创建表 Roles 任务"
oStep.CommitSuccess = False
oStep.RollbackFailure = False
oStep.ScriptLanguage = "VBScript"
oStep.AddGlobalVariables = True
oStep.RelativePriority = 3
oStep.CloseConnection = False
oStep.ExecuteInMainThread = False
oStep.IsPackageDSORowset = False
oStep.JoinTransactionIfPresent = False
oStep.DisableStep = False
oStep.FailPackageOnError = False

goPackage.Steps.Add oStep
Set oStep = Nothing

'------------- a new step defined below

Set oStep = goPackage.Steps.New

oStep.Name = "Copy Data from Roles to Roles 步骤"
oStep.Description = "Copy Data from Roles to Roles 步骤"
oStep.ExecutionStatus = 1
oStep.TaskName = "Copy Data from Roles to Roles 任务"
oStep.CommitSuccess = False
oStep.RollbackFailure = False
oStep.ScriptLanguage = "VBScript"
oStep.AddGlobalVariables = True
oStep.RelativePriority = 3
oStep.CloseConnection = False
oStep.ExecuteInMainThread = True
oStep.IsPackageDSORowset = False
oStep.JoinTransactionIfPresent = False
oStep.DisableStep = False
oStep.FailPackageOnError = False

goPackage.Steps.Add oStep
Set oStep = Nothing

'------------- a precedence constraint for steps defined below

Set oStep = goPackage.Steps("Copy Data from Roles to Roles 步骤")
Set oPrecConstraint = oStep.PrecedenceConstraints.New("创建表 Roles 步骤")
oPrecConstraint.StepName = "创建表 Roles 步骤"
oPrecConstraint.PrecedenceBasis = 0
oPrecConstraint.Value = 4

oStep.precedenceConstraints.Add oPrecConstraint
Set oPrecConstraint = Nothing

'---------------------------------------------------------------------------
' create package tasks information
'---------------------------------------------------------------------------

'------------- call Task_Sub1 for task 创建表 Roles 任务 (创建表 Roles 任务)
Call Task_Sub1( goPackage )

'------------- call Task_Sub2 for task Copy Data from Roles to Roles 任务 (Copy Data from Roles to Roles 任务)
Call Task_Sub2( goPackage )

'---------------------------------------------------------------------------
' Save or execute package
'---------------------------------------------------------------------------

'goPackage.SaveToSQLServer "(local)", "sa", ""
goPackage.Execute
goPackage.Uninitialize
'to save a package instead of executing it, comment out the executing package line above and uncomment the saving package line
set goPackage = Nothing

set goPackageOld = Nothing

End Sub


'------------- define Task_Sub1 for task 创建表 Roles 任务 (创建表 Roles 任务)
Public Sub Task_Sub1(ByVal goPackage As Object)

Dim oTask As DTS.Task
Dim oLookup As DTS.Lookup

Dim oCustomTask1 As DTS.ExecuteSQLTask2
Set oTask = goPackage.Tasks.New("DTSExecuteSQLTask")
Set oCustomTask1 = oTask.CustomTask

oCustomTask1.Name = "创建表 Roles 任务"
oCustomTask1.Description = "创建表 Roles 任务"
oCustomTask1.SQLStatement = "CREATE TABLE `Roles` (" & vbCrLf
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "`ID` VarChar (3) NOT NULL, " & vbCrLf
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "`Read` VarChar (1) NOT NULL, " & vbCrLf
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "`Write` VarChar (1) NOT NULL, " & vbCrLf
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "`Print` VarChar (1) NOT NULL, " & vbCrLf
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "`Super` VarChar (1) NOT NULL, " & vbCrLf
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & "`Description` VarChar (128) NULL" & vbCrLf
oCustomTask1.SQLStatement = oCustomTask1.SQLStatement & ")"
oCustomTask1.ConnectionID = 2
oCustomTask1.CommandTimeout = 0
oCustomTask1.OutputAsRecordset = False

goPackage.Tasks.Add oTask
Set oCustomTask1 = Nothing
Set oTask = Nothing

End Sub

'------------- define Task_Sub2 for task Copy Data from Roles to Roles 任务 (Copy Data from Roles to Roles 任务)
Public Sub Task_Sub2(ByVal goPackage As Object)

Dim oTask As DTS.Task
Dim oLookup As DTS.Lookup

Dim oCustomTask2 As DTS.DataPumpTask2
Set oTask = goPackage.Tasks.New("DTSDataPumpTask")
Set oCustomTask2 = oTask.CustomTask

oCustomTask2.Name = "Copy Data from Roles to Roles 任务"
oCustomTask2.Description = "Copy Data from Roles to Roles 任务"
oCustomTask2.SourceConnectionID = 1
oCustomTask2.SourceSQLStatement = "select [ID],[Read],[Write],[Print],[Super],[Description] from [ZCSoftMRP].[dbo].[Roles]"
oCustomTask2.DestinationConnectionID = 2
oCustomTask2.DestinationObjectName = "Roles"
oCustomTask2.ProgressRowCount = 1000
oCustomTask2.MaximumErrorCount = 0
oCustomTask2.FetchBufferSize = 1
oCustomTask2.UseFastLoad = True
oCustomTask2.InsertCommitSize = 0
oCustomTask2.ExceptionFileColumnDelimiter = "|"
oCustomTask2.ExceptionFileRowDelimiter = vbCrLf
oCustomTask2.AllowIdentityInserts = False
oCustomTask2.FirstRow = 0
oCustomTask2.LastRow = 0
oCustomTask2.FastLoadOptions = 2
oCustomTask2.ExceptionFileOptions = 1
oCustomTask2.DataPumpOptions = 0

Call oCustomTask2_Trans_Sub1( oCustomTask2 )


goPackage.Tasks.Add oTask
Set oCustomTask2 = Nothing
Set oTask = Nothing

End Sub

Public Sub oCustomTask2_Trans_Sub1(ByVal oCustomTask2 As Object)

Dim oTransformation As DTS.Transformation2
Dim oTransProps as DTS.Properties
Dim oColumn As DTS.Column
Set oTransformation = oCustomTask2.Transformations.New("DTS.DataPumpTransformCopy")
oTransformation.Name = "DirectCopyXform"
oTransformation.TransformFlags = 63
oTransformation.ForceSourceBlobsBuffered = 0
oTransformation.ForceBlobsInMemory = False
oTransformation.InMemoryBlobSize = 1048576
oTransformation.TransformPhases = 4

Set oColumn = oTransformation.SourceColumns.New("ID" , 1)
oColumn.Name = "ID"
oColumn.Ordinal = 1
oColumn.Flags = 24
oColumn.Size = 3
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("Read" , 2)
oColumn.Name = "Read"
oColumn.Ordinal = 2
oColumn.Flags = 24
oColumn.Size = 1
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("Write" , 3)
oColumn.Name = "Write"
oColumn.Ordinal = 3
oColumn.Flags = 24
oColumn.Size = 1
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("Print" , 4)
oColumn.Name = "Print"
oColumn.Ordinal = 4
oColumn.Flags = 24
oColumn.Size = 1
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("Super" , 5)
oColumn.Name = "Super"
oColumn.Ordinal = 5
oColumn.Flags = 24
oColumn.Size = 1
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("Description" , 6)
oColumn.Name = "Description"
oColumn.Ordinal = 6
oColumn.Flags = 104
oColumn.Size = 128
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("ID" , 1)
oColumn.Name = "ID"
oColumn.Ordinal = 1
oColumn.Flags = 24
oColumn.Size = 3
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("Read" , 2)
oColumn.Name = "Read"
oColumn.Ordinal = 2
oColumn.Flags = 24
oColumn.Size = 1
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("Write" , 3)
oColumn.Name = "Write"
oColumn.Ordinal = 3
oColumn.Flags = 24
oColumn.Size = 1
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("Print" , 4)
oColumn.Name = "Print"
oColumn.Ordinal = 4
oColumn.Flags = 24
oColumn.Size = 1
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("Super" , 5)
oColumn.Name = "Super"
oColumn.Ordinal = 5
oColumn.Flags = 24
oColumn.Size = 1
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = False

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("Description" , 6)
oColumn.Name = "Description"
oColumn.Ordinal = 6
oColumn.Flags = 104
oColumn.Size = 128
oColumn.DataType = 130
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oTransProps = oTransformation.TransformServerProperties


Set oTransProps = Nothing

oCustomTask2.Transformations.Add oTransformation
Set oTransformation = Nothing

End Sub
 
这是从foxpro倒sql server的,你看看,能不能改改
---- 基 本 思 想 是: 在 一 个Form 中, 分 别 用 两 个TDatabase 控 件 连 接 新 老 数 据 库。 并 采 用 TTable、TDbGrid 作 为 数 据 转 移 的 中 心, 根 据DbGrid 中 的 数 据 生 成 标 准 的SQL 插 入 语 句。 这 样, 就 实 现 了 从 一 个 数 据 库 系 统 到 另 一 个 数 据 库 系 统 的 数 据 转 移。 在 这 里, 采 用TTable、TDbGrid 作 为 数 据 转 移 的 中 心 是 一 个 技 巧, 因 为:TTable 的Fields 属 性 能 指 示 出 某 字 段 的 字 段 名 称、 数 据 类 型 等, 这 为 数 据 转 移 过 程 中 的Insert 语 句 的 生 成 及 数 据 类 型 转 换 提 供 了 依 据。

---- 下 面 的 例 子 展 示 了 从Foxpro 到SQL Server 的 数 据 转 移 方 法。 至 于 其 他 系 统 间 的 数 据 转 移, 只 要 根 据 目 标 系 统 的 数 据 定 义 要 求, 修 改 相 应 的Insert 语 句。

---- 程 序 代 码 如 下:

unit ConvertDBF;

interface

uses
Windows, Messages, SysUtils, Classes,
Graphics, Controls, Forms, Dialogs,
StdCtrls, DBTables, Db, Grids, DBGrids;

type
TfrmConvertDB = class(TForm)
btnOK: TButton;
Label1: TLabel;
db1: TDatabase; {用于连接老数据库系统}
db2: TDatabase; {用于连接新数据库系统}
dbg: TDBGrid;
tblSource: TTable; {dbg的Datasource}
qryInsert: TQuery;
{用于存放生成的SQL Insert语句}
srcSource: TDataSource;
tblDest: TTable; {DBGrid1的Datasource}
DBGrid1: TDBGrid;
srcDest: TDataSource;
edFromtbl: TEdit;
Label2: TLabel;
Label3: TLabel;
edToTbl: TEdit;
procedure btnOKClick(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;

var
frmConvertDB: TfrmConvertDB;

implementation

{$R *.DFM}
procedure TfrmConvertDB.btnOKClick
(Sender: TObject);
var iField :integer;
begin
if ((edTotbl.text<>'') and
(edFromtbl.text<>''))then begin
tblSource.TableName:=edFromtbl.text;
{指定TableName}
tblDest.TableName:=edTotbl.text;
with tblSource do begin
Open; {打开老系统的表}
while EOF=FALSE do begin
{逐条记录处理}
qryInsert.SQL.Clear;
qryInsert.sql.Add
('Insert into '+edTotbl.text + '(');
for iField:=0 to dbg.FieldCount-1 do begin
qryInsert.sql.add
(dbg.Fields[iField].DisplayLabel);
if iField<>dbg.FieldCount-1 then
qryInsert.sql.add(',');
end;
qryInsert.sql.add(') values(');
for iField:=0 to dbg.FieldCount-1 do begin
{进行数据类型转换}
if dbg.fields[iField].DataType=ftInteger then
qryInsert.sql.add(inttostr
(dbg.fields[iField].asInteger));
if dbg.fields[iField].DataType=ftFloat then
qryInsert.sql.add(floattostr
(dbg.fields[iField].asFloat));
if dbg.fields[iField].DataType=ftDate then
qryInsert.sql.add(''''+datetostr
(dbg.fields[iField].asDateTime)+'''');
if dbg.fields[iField].DataType=ftString then begin
if dbg.fields[iField].asString<>'' then
qryInsert.sql.add(''''+dbg.fields
[iField].asString+'''')
else
qryInsert.sql.add('NULL');
end;
if iField<>dbg.FieldCount-1
then qryInsert.sql.add(',');
end;
qryInsert.sql.add(')');
qryInsert.ExecSQL;
{把数据插入到新系统的表中}
next;
end;
end;
tblDest.Close;
tblDest.Open;;
ShowMessage(' 转换完毕! ');
end
else
ShowMessage
('请输入要插入数据的表的名称 ');
end;
end.
 
TO Adnil:
可是如果操作系统是98,它是没有组件服务的,如何解决要?
 
98中没有mts,com组件还是有的。
 
顶部