■ 如何 向 数据库 中 批量 添加记录

[zzlcn]

■ 如何 向 sql server 数据库 中 批量 添加记录

大概有100万 条记录要 添加到数据库中

一条一条的添加 实在太慢

有没有什么办法 一次 添加 1万 条 或者 1千 条也行

只要速度快些就好!

[lsftest]

把txt作为数据源读入数据，然后全部insert into目标数据库。。。。。。

[faysky2]

想快速，高效，用 DTS来做（在手动导入过程生成DTS包，然后可以直接在vb里调用）

在vb里调用需要引用 Microsoft DTSPackage Object Library

[faysky2]

'将C:\Test.txt（每行有3个逗号作为分隔符）到入到sql的db1的Tab1表里（表有4列，分别为A B C D）

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("DTSFlatFile")

oConnection.ConnectionProperties("Data Source") = "C:\Test.txt"
oConnection.ConnectionProperties("Mode") = 1
oConnection.ConnectionProperties("Row Delimiter") = vbCrLf
oConnection.ConnectionProperties("File Format") = 1
oConnection.ConnectionProperties("Column Delimiter") = ","
oConnection.ConnectionProperties("File Type") = 1
oConnection.ConnectionProperties("Skip Rows") = 0
oConnection.ConnectionProperties("Text Qualifier") = """"
oConnection.ConnectionProperties("First Row Column Name") = False

oConnection.Name = "连接1"
oConnection.ID = 1
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "C:\Test.txt"
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

'------------- 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") = "db1"
oConnection.ConnectionProperties("Data Source") = "(LOCAL)"
oConnection.ConnectionProperties("Application Name") = "DTS 导入/导出向导"

oConnection.Name = "连接2"
oConnection.ID = 2
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "(LOCAL)"
oConnection.UserID = "sa"
oConnection.ConnectionTimeout = 60
oConnection.Catalog = "db1"
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 = "Copy Data from Test to [db1].[dbo].[TAB1] 步骤"
oStep.Description = "Copy Data from Test to [db1].[dbo].[TAB1] 步骤"
oStep.ExecutionStatus = 1
oStep.TaskName = "Copy Data from Test to [db1].[dbo].[TAB1] 任务"
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

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

'------------- call Task_Sub1 for task Copy Data from Test.txt to [db1].[dbo].[TAB1] 任务 (Copy Data from Test.txt to [db1].[dbo].[TAB1] 任务)
Call Task_Sub1(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 Copy Data from Test.txt to [db1].[dbo].[TAB1] 任务 (Copy Data from Test.txt to [db1].[dbo].[TAB1] 任务)
Public Sub Task_Sub1(ByVal goPackage As Object)

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

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

oCustomTask1.Name = "Copy Data from Test to [db1].[dbo].[TAB1] 任务"
oCustomTask1.Description = "Copy Data from Test.txt to [db1].[dbo].[TAB1] 任务"
oCustomTask1.SourceConnectionID = 1
oCustomTask1.SourceObjectName = "C:\Test.txt"
oCustomTask1.DestinationConnectionID = 2
oCustomTask1.DestinationObjectName = "[db1].[dbo].[TAB1]"
oCustomTask1.ProgressRowCount = 1000
oCustomTask1.MaximumErrorCount = 0
oCustomTask1.FetchBufferSize = 1
oCustomTask1.UseFastLoad = True
oCustomTask1.InsertCommitSize = 0
oCustomTask1.ExceptionFileColumnDelimiter = "|"
oCustomTask1.ExceptionFileRowDelimiter = vbCrLf
oCustomTask1.AllowIdentityInserts = False
oCustomTask1.FirstRow = 0
oCustomTask1.LastRow = 0
oCustomTask1.FastLoadOptions = 2
oCustomTask1.ExceptionFileOptions = 1
oCustomTask1.DataPumpOptions = 0

Call oCustomTask1_Trans_Sub1(oCustomTask1)


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

End Sub

[faysky2]

接上：

Public Sub oCustomTask1_Trans_Sub1(ByVal oCustomTask1 As Object)

Dim oTransformation As DTS.Transformation2
Dim oTransProps As DTS.Properties
Dim oColumn As DTS.Column
Set oTransformation = oCustomTask1.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("Col001", 1)
oColumn.Name = "Col001"
oColumn.Ordinal = 1
oColumn.Flags = 32
oColumn.Size = 255
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("Col002", 2)
oColumn.Name = "Col002"
oColumn.Ordinal = 2
oColumn.Flags = 32
oColumn.Size = 255
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("Col003", 3)
oColumn.Name = "Col003"
oColumn.Ordinal = 3
oColumn.Flags = 32
oColumn.Size = 255
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.SourceColumns.New("Col004", 4)
oColumn.Name = "Col004"
oColumn.Ordinal = 4
oColumn.Flags = 32
oColumn.Size = 255
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.SourceColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("A", 1)
oColumn.Name = "A"
oColumn.Ordinal = 1
oColumn.Flags = 104
oColumn.Size = 50
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("B", 2)
oColumn.Name = "B"
oColumn.Ordinal = 2
oColumn.Flags = 104
oColumn.Size = 50
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("C", 3)
oColumn.Name = "C"
oColumn.Ordinal = 3
oColumn.Flags = 104
oColumn.Size = 50
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oColumn = oTransformation.DestinationColumns.New("D", 4)
oColumn.Name = "D"
oColumn.Ordinal = 4
oColumn.Flags = 104
oColumn.Size = 50
oColumn.DataType = 129
oColumn.Precision = 0
oColumn.NumericScale = 0
oColumn.Nullable = True

oTransformation.DestinationColumns.Add oColumn
Set oColumn = Nothing

Set oTransProps = oTransformation.TransformServerProperties


Set oTransProps = Nothing

oCustomTask1.Transformations.Add oTransformation
Set oTransformation = Nothing

End Sub

[fishmans]

用sql的 opendatasource()函数

以ODBC连接这个文本

如：
insert into table1 select * from opendatasource(ODBC驱动，其他字串)..文件名

[Free_Windy]

insert into newtb select * from oldtb

[ZOU_SEAFARER]

原数据在那里？
access数据库里面，txt文本文件里面还是
就是说你的数据以前在那里存储的？

[faysky2]

同上

[zzlcn]

在文本文件中
每行一条记录
用 , 分隔

[ZOU_SEAFARER]

我能使用的方法就是一条一条的处理，肯定效率很底
等待 faysky2() 来回答吧，他是这里的高手，
好象可以使用数据库的方式打开txt文件，以后的操作我也不晓得了，呵呵

[TechnoFantasy]

需要看源数据是什么格式的，你可以参考这个不同类型数据通过编程互倒的文章：

http://www.microsoft.com/china/community/Column/31.mspx

[zzlcn]

上面的代码太长了!

请继续讨论

[faysky2]

短的可以这样：

'引用Excel（"工程"/"引用"/Microsoft Excel Object X.0 Library）
Private Sub Command1_Click()
Dim cn As New ADODB.Connection

cn.Open "Provider=SQLOLEDB.1;Password=;Persist Security Info=False;User ID=sa;Initial Catalog=db1;Data Source=image"

cn.Execute ("BULK INSERT Table1 From 'C:\Test.txt' WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n')")

cn.Close
Set cn = Nothing
End Sub

[faysky2]

上面的引用写错了
引用ADO("工程"/"引用"/Microsoft ActiveX Data Object 2.X Library)