'当然,你也可以定义两个连接分别到sqlserver与excel文件,然后定义两个纪录集打开对应的表,你就可以相互赋值从而达到导入的目的,这种速度会慢些,优点是excel文件不需要存在同一个电脑上(上述方法需要) '引用Microsoft Activex Data Object2.x Library 与 Microsoft Excel Object9.0(或以上) Library Dim cnSqlserver As ADODB.Connection Dim cnExcel As ADODB.Connection Dim rsExcel As ADODB.Recordset Dim rsSqlserver As ADODB.Recordset Set cnExcel = New ADODB.Connection cnExcel.CursorLocation = adUseClient '连接到Excel cnExcel.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\test.xls;Extended Properties=Excel 8.0;Persist Security Info=true"
Set cnSqlserver = New ADODB.Connection cnSqlserver.CursorLocation = adUseClient '连接到Sqlserver cnSqlserver.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\test.xls;Extended Properties=Excel 8.0;Persist Security Info=true"
Set rsExcel = New ADODB.Recordset '打开Excel中的源表 rsExcel.Open "table1", cnExcel, adOpenKeyset, adLockOptimistic
Set rsSqlserver = New ADODB.Recordset '打开Sqlserver的目的表 rsSqlserver.Open "select * from table2 where 1=2", cnSqlserver, adOpenKeyset, adLockOptimistic, adCmdText Do While Not rsExcel.EOF '将源表中的纪录赋给目的表 rsSqlserver.AddNew rsSqlserver!ID = rsExcel!ID rsSqlserver!Name = rsExcel!Name '... '... '... rsSqlserver.Update rsExcel.MoveNext Loop rsExcel.Close rsSqlserver.Close Set rsExcel = Nothing Set rsSqlserver = Nothing cnExcel.Close cnSqlserver.Close Set cnExcel = Nothing Set cnSqlserver = Nothing
另一种方法: cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\test.xls;Extended Properties=Excel 8.0"’如果这张表不存在,你可以使用如下代码:strSQL = "SELECT * INTO [odbc;Driver={SQL Server};Server=sha-kennyhao-01;Database=Northwind;UID=sa;PWD=;].Customers2 FROM [Sheet1$]"cn.Execute strSQL, , adExecuteNoRecords如果表已经存在,您需要添加进数据库,可以使用如下代码:strSQL = "INSERT INTO [odbc;Driver={SQL Server};Server=sha-kennyhao-01;Database=Northwind;UID=sa;PWD=;].Customers2 SELECT * FROM [Sheet1$]"cn.Execute strSQL, , adExecuteNoRecords
SQL语句导入导出大全http://expert.csdn.net/Expert/topic/2461/2461899.xml?temp=.39555
交流--导入/导出Excel
/******* 导出到excel
EXEC master..xp_cmdshell 'bcp SettleDB.dbo.shanghu out c:\temp1.xls -c -q -S"GNETDATA/GNETDATA" -U"sa" -P""'/*********** 导入Excel
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
'引用Microsoft Activex Data Object2.x Library 与 Microsoft Excel Object9.0(或以上) Library
Dim cnSqlserver As ADODB.Connection
Dim cnExcel As ADODB.Connection
Dim rsExcel As ADODB.Recordset
Dim rsSqlserver As ADODB.Recordset Set cnExcel = New ADODB.Connection
cnExcel.CursorLocation = adUseClient
'连接到Excel
cnExcel.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\test.xls;Extended Properties=Excel 8.0;Persist Security Info=true"
Set cnSqlserver = New ADODB.Connection
cnSqlserver.CursorLocation = adUseClient
'连接到Sqlserver
cnSqlserver.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\test.xls;Extended Properties=Excel 8.0;Persist Security Info=true"
Set rsExcel = New ADODB.Recordset
'打开Excel中的源表
rsExcel.Open "table1", cnExcel, adOpenKeyset, adLockOptimistic
Set rsSqlserver = New ADODB.Recordset
'打开Sqlserver的目的表
rsSqlserver.Open "select * from table2 where 1=2", cnSqlserver, adOpenKeyset, adLockOptimistic, adCmdText Do While Not rsExcel.EOF
'将源表中的纪录赋给目的表
rsSqlserver.AddNew
rsSqlserver!ID = rsExcel!ID
rsSqlserver!Name = rsExcel!Name
'...
'...
'...
rsSqlserver.Update
rsExcel.MoveNext
Loop
rsExcel.Close
rsSqlserver.Close
Set rsExcel = Nothing
Set rsSqlserver = Nothing
cnExcel.Close
cnSqlserver.Close
Set cnExcel = Nothing
Set cnSqlserver = Nothing
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\test.xls;Extended Properties=Excel 8.0"’如果这张表不存在,你可以使用如下代码:strSQL = "SELECT * INTO [odbc;Driver={SQL Server};Server=sha-kennyhao-01;Database=Northwind;UID=sa;PWD=;].Customers2 FROM [Sheet1$]"cn.Execute strSQL, , adExecuteNoRecords如果表已经存在,您需要添加进数据库,可以使用如下代码:strSQL = "INSERT INTO [odbc;Driver={SQL Server};Server=sha-kennyhao-01;Database=Northwind;UID=sa;PWD=;].Customers2 SELECT * FROM [Sheet1$]"cn.Execute strSQL, , adExecuteNoRecords