'数据库与文本文件的导入导出 Private Sub Form_Load() Dim cn As New ADODB.Connection If cn.State = 1 Then cn.Close End If cn.CursorLocation = adUseClient cn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;pwd=;Initial Catalog=学生;Data Source=MyServer" cn.Open
'导出 cn.Execute "SELECT * into [Text;HDR=YES;DATABASE=d:\].a.txt from students" '导入 cn.Execute "insert into students select * from [Text;HDR=YES;DATABASE=d:\].a.txt" End Sub
导入的,使用bulk insert效果不错速度快,将下列代码放入存储过程,vb调用即可本例从指定的数据文件中导入订单详细信息,该文件使用竖杠 (|) 字符作为字段终止符,使用 |\n 作为行终止符。BULK INSERT Northwind.dbo.[Order Details] FROM 'f:\orders\lineitem.txt' WITH ( FIELDTERMINATOR = '|', ROWTERMINATOR = '|\n' )本例指定 FIRE_TRIGGERS 参数。BULK INSERT Northwind.dbo.[Order Details] FROM 'f:\orders\lineitem.txt' WITH ( FIELDTERMINATOR = '|', ROWTERMINATOR = ':\n', FIRE_TRIGGERS )
Private Sub Form_Load()
Dim cn As New ADODB.Connection
If cn.State = 1 Then
cn.Close
End If
cn.CursorLocation = adUseClient
cn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;pwd=;Initial Catalog=学生;Data Source=MyServer"
cn.Open
'导出
cn.Execute "SELECT * into [Text;HDR=YES;DATABASE=d:\].a.txt from students"
'导入
cn.Execute "insert into students select * from [Text;HDR=YES;DATABASE=d:\].a.txt"
End Sub
FROM 'f:\orders\lineitem.txt'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '|\n'
)本例指定 FIRE_TRIGGERS 参数。BULK INSERT Northwind.dbo.[Order Details]
FROM 'f:\orders\lineitem.txt'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = ':\n',
FIRE_TRIGGERS
)