Vb.net SQL批量插入/复制 [英] Vb.net SQL bulk insert/copy
问题描述
希望有人可以提供帮助,
背景:有一台旧机器将数据放入MS访问文件,我们需要将表导入到SQL表中,访问数据库保持滚动3天的数据,然后删除旧数据,想法是每天早上运行一个任务,将新记录插入到sql中,忽略现有数据,这样我们就可以在一年中构建一个图形。
我已经从MS访问中将数据提取到一个名为table的数据表中,并在匹配的SQL表中创建了列名,我读过有关SQL Bulkcopy但是所有的我见过的样本都在C#中,但是任何代码转换器都出错了,我不是100%,他们会在以后做什么。
有人可以帮忙吗?
Pete
我的尝试:
Hope someone could help,
Background: Got an old machine that puts data into an MS access file and we need to import the table into an SQL table, the access database keeps a rolling 3 days of data and then deletes the older data, the idea is to run a task every morning to insert the new records to sql, ignore existing so we can build a graph over the year.
I've extracted data from MS access into a Datatable called "table" and created the column names in an SQL table which matches, i've read about SQL Bulkcopy but all of the samples i've seen are in C# but any code converters error out and im not 100% that they'll do what im after.
Could anyone help out?
Pete
What I have tried:
Dim count As Integer = 0
Dim table As DataTable = New DataTable
Dim accConnection As New OleDb.OleDbConnection("Provider=Microsoft.JET.OLEDB.4.0; Data Source='C:\Machine.mdb';User Id=admin; Password=;")
Dim sqlConnection As New SqlClient.SqlConnection("Data Source=10.75.24.94;Initial Catalog=CTData;User ID=sql;Password=")
Try
'Import the Access data
accConnection.Open()
Dim accDataAdapter = New OleDb.OleDbDataAdapter("SELECT * FROM Slot_Vision_Counters_table", accConnection)
accDataAdapter.Fill(table)
accConnection.Close()
'Export to MS SQL
For Each row As DataRow In table.Rows
row.SetAdded()
count = count + 1
Next
### Code for inserting the data to SQL?
Catch ex As Exception
If accConnection.State = ConnectionState.Open Then
accConnection.Close()
End If
If sqlConnection.State = ConnectionState.Open Then
sqlConnection.Close()
End If
MessageBox.Show("Import failed with error: " & Environment.NewLine & Environment.NewLine _
& ex.ToString)
End Try
推荐答案
参见此处的示例:使用SqlBulkCopy和Visual Basic传输数据 [ ^ ]
See example here: Transferring Data Using SqlBulkCopy and Visual Basic[^]
这篇关于Vb.net SQL批量插入/复制的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!