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

zzlcn 2006-02-15 11:28:12

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

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

一条一条的添加 实在太慢

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

只要速度快些就好!
...全文
130 点赞 收藏 14
写回复
14 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
faysky2 2006-02-17
上面的引用写错了
引用ADO("工程"/"引用"/Microsoft ActiveX Data Object 2.X Library)
回复
faysky2 2006-02-17
短的可以这样:

'引用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
回复
zzlcn 2006-02-17
上面的代码太长了!

请继续讨论
回复
TechnoFantasy 2006-02-15
需要看源数据是什么格式的,你可以参考这个不同类型数据通过编程互倒的文章:

http://www.microsoft.com/china/community/Column/31.mspx
回复
ZOU_SEAFARER 2006-02-15
我能使用的方法就是一条一条的处理,肯定效率很底
等待 faysky2() 来回答吧,他是这里的高手,
好象可以使用数据库的方式打开txt文件,以后的操作我也不晓得了,呵呵
回复
zzlcn 2006-02-15
在文本文件中
每行一条记录
用 , 分隔
回复
faysky2 2006-02-15
同上
回复
ZOU_SEAFARER 2006-02-15
原数据在那里?
access数据库里面,txt文本文件里面还是
就是说你的数据以前在那里存储的?
回复
Free_Windy 2006-02-15
insert into newtb select * from oldtb
回复
fishmans 2006-02-15
用sql的 opendatasource()函数

以ODBC连接这个文本

如:
insert into table1 select * from opendatasource(ODBC驱动,其他字串)..文件名
回复
faysky2 2006-02-15
接上:

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

回复
faysky2 2006-02-15
'将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 2006-02-15
想快速,高效,用 DTS来做(在手动导入过程生成DTS包,然后可以直接在vb里调用)

在vb里调用需要引用 Microsoft DTSPackage Object Library
回复
lsftest 2006-02-15
把txt作为数据源读入数据,然后全部insert into目标数据库。。。。。。
回复
相关推荐
发帖

1188

社区成员

VB 数据库(包含打印,安装,报表)
申请成为版主
帖子事件
创建了帖子
2006-02-15 11:28
社区公告
暂无公告