Excel导入SQL Server'引用ADO(Microsoft ActiveX Data Objects 2.X Library) Private Sub Command1_Click() On Error GoTo err Dim cn As New ADODB.Connection cn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Password=密码;Initial Catalog=数据库;Data Source=服务器别名" cn.CursorLocation = adUseClient cn.Open cn.Execute "select * into table1 from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;HDR=Yes;database=c:\Test.xls;','select * from [a b c$]')" 'Excel中的"a b c"表导入SQL的"table1"表 Exit Sub err: MsgBox err.Description End Sub
使用程序导入可以用 foreverstar2004 的方法,也可以在企业管理器中直接导入.
在查询分析器中运行:insert into 数据库表名 select * from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;HDR=yes;database=EXCEL路径;','select * from [Sheet1$]')在运行时注意要关闭EXCEL文件
如果sql里表已经存在,则用insert into 语句插入,如果用select * into 语句则会出错用select * into table1 语句的前提是sql数据库里不存在table1这个表
Private Sub Command1_Click()
On Error GoTo err
Dim cn As New ADODB.Connection
cn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Password=密码;Initial Catalog=数据库;Data Source=服务器别名"
cn.CursorLocation = adUseClient
cn.Open
cn.Execute "select * into table1 from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;HDR=Yes;database=c:\Test.xls;','select * from [a b c$]')" 'Excel中的"a b c"表导入SQL的"table1"表
Exit Sub
err:
MsgBox err.Description
End Sub
select * from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;HDR=yes;database=EXCEL路径;','select * from [Sheet1$]')在运行时注意要关闭EXCEL文件