'功能:从EXCEL文件导入数据表
Dim cnnDB As ADODB.Connection
Dim strSheet As String
Dim recXls As ADODB.Recordset
Dim recInsert As ADODB.Recordset
On Error GoTo er:
CnMain.BeginTrans
Screen.MousePointer = vbHourglass
lngCount = 0
ExcelIn = False
Set cnnDB = New ADODB.Connection
With cnnDB
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Extended Properties") = "Excel 8.0;IMEX=1;"
.Open txtFile.Text
End With
Set recXls = New ADODB.Recordset
strSheet = txtSheet.Text
recXls.Open "Select * From [" & strSheet & "$]", cnnDB, adOpenDynamic, adLockOptimistic
lngCount = 0
Set recInsert = New Recordset
recInsert.Open "select * from " & strTable & " where 1 = 0", CnMain, adOpenStatic, adLockBatchOptimistic
While Not recXls.EOF
With recInsert
.AddNew
.Fields("Code").Value = "" & recXls![箱号]
.Fields("AcCodeBoxName").Value = "" & recXls![原材料成品箱头]
.Fields("TempMark").Value = 0
.Update
End With
lngCount = lngCount + 1
recXls.MoveNext
Wend
recXls.Close
Set recXls = Nothing
recInsert.UpdateBatch '全数批量更新
recInsert.Close
Set recInsert = Nothing
Dim cnnDB As ADODB.Connection
Dim strSheet As String
Dim recXls As ADODB.Recordset
Dim recInsert As ADODB.Recordset
On Error GoTo er:
CnMain.BeginTrans
Screen.MousePointer = vbHourglass
lngCount = 0
ExcelIn = False
Set cnnDB = New ADODB.Connection
With cnnDB
.Provider = "Microsoft.Jet.OLEDB.4.0"
.Properties("Extended Properties") = "Excel 8.0;IMEX=1;"
.Open txtFile.Text
End With
Set recXls = New ADODB.Recordset
strSheet = txtSheet.Text
recXls.Open "Select * From [" & strSheet & "$]", cnnDB, adOpenDynamic, adLockOptimistic
lngCount = 0
Set recInsert = New Recordset
recInsert.Open "select * from " & strTable & " where 1 = 0", CnMain, adOpenStatic, adLockBatchOptimistic
While Not recXls.EOF
With recInsert
.AddNew
.Fields("Code").Value = "" & recXls![箱号]
.Fields("AcCodeBoxName").Value = "" & recXls![原材料成品箱头]
.Fields("TempMark").Value = 0
.Update
End With
lngCount = lngCount + 1
recXls.MoveNext
Wend
recXls.Close
Set recXls = Nothing
recInsert.UpdateBatch '全数批量更新
recInsert.Close
Set recInsert = Nothing
最后要提交事务,其实可以不用事务,因为我用的是批处理游标.
SELECT * FROM OPENROWSET('MSDASQL.1', 'driver=Microsoft Excel Driver (*.xls);DBQ=c:\boox.xls','select * from [工作表名]')
直接执行查询语句:insert into szdn..rk select * from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;IMEX=2;DATABASE=c:\boox.xls',x1$)
EXEC master..xp_cmdshell 'bcp 数据库名.dbo.表名 out c:\table1.xls -c -q -S 实例名 -U 用户名 -P 口令
--导入
EXEC master..xp_cmdshell 'bcp 数据库名.dbo.表名 in c:\table1.xls -c -q -S 实例名 -U 用户名 -P 口令
仅当使用了列的列表,并且 IDENTITY_INSERT 为 ON 时,才能在表 'szdn.dbo.rk' 中为标识列指定显式值。
怎么才能实现重EXCLE中读取的值 ,插入到指定的位置
用DTS不更简单吗
我可不能在互联网上操作SQL SERVER 2000的DTS吧