怎样使用T-SQL语句实现将Excel文件中的数据导入到SQL2005数据库对应的表中

MyITdream 2010-04-25 10:23:35
现在我有大量的数据存储在Excel文件中，而且Excel中的字段都是牵扯到多个表，现在想使用T-SQL语句实现将Excel文件中的数据导入到SQL2005数据库对应的表中，请高手给段代码。
...全文
599 5 打赏 收藏 转发到动态 举报
写回复
用AI写文章
5 条回复
切换为时间正序
请发表友善的回复…
发表回复
--小F-- 2010-04-25
  • 打赏
  • 举报
 回复
bulk insert ??
htl258_Tony 2010-04-25
  • 打赏
  • 举报
 回复
[Quote=引用楼主 myitdream 的回复:]
现在我有大量的数据存储在Excel文件中，而且Excel中的字段都是牵扯到多个表，现在想使用T-SQL语句实现将Excel文件中的数据导入到SQL2005数据库对应的表中，请高手给段代码。
[/Quote]
反问两个问题：
1.对应表是怎么对应法（导入后的表名和EXCEL文件名一样吗，还是什么规则？）
2.每个要导入的EXCEL文件是否单一工作表，工作表名是否都统一（如:Sheet1，如果不统一，也比较麻烦）
dawugui 2010-04-25
  • 打赏
  • 举报
 回复 
/******* 导出到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 



/*动态文件名 

declare @fn varchar(20),@s varchar(1000) 

set @fn = 'c:\test.xls' 

set @s ='''Microsoft.Jet.OLEDB.4.0'', 

''Data Source="'+@fn+'";User ID=Admin;Password=;Extended properties=Excel 5.0''' 

set @s = 'SELECT * FROM OpenDataSource ('+@s+')...sheet1$' 

exec(@s) 

*/ 



SELECT cast(cast(科目编号 as numeric(10,2)) as nvarchar(255))+'　' 转换后的别名 

FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 

'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions 



/********************** EXCEL导到远程SQL 

insert OPENDATASOURCE( 

'SQLOLEDB', 

'Data Source=远程ip;User ID=sa;Password=密码' 

).库名.dbo.表名 (列名1,列名2) 

SELECT 列名1,列名2 

FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 

'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
喜-喜 2010-04-25
  • 打赏
  • 举报
 回复 
Transact-SQL语句进行导入导出：



1.在SQL SERVER里查询access数据:

-- ======================================================

SELECT * 

FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

'Data Source="c:\DB.mdb";User ID=Admin;Password=')...表名



select *  from opendatasource('Microsoft.Jet.OLEDB.4.0','Data Source="D:\db.mdb";User ID=Admin;Password=')...Student where name = '我'

select * from newTable

-------------------------------------------------------------------------------------------------

2.将access导入SQL server 

-- ======================================================

在SQL SERVER 里运行:

SELECT *

INTO newtable

FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', 

      'Data Source="c:\DB.mdb";User ID=Admin;Password=' )...表名

-------------------------------------------------------------------------------------------------

3.将SQL SERVER表里的数据插入到Access表中

-- ======================================================

在SQL SERVER 里运行：

insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

  'Data Source=" c:\DB.mdb";User ID=Admin;Password=')...表名 

(列名1,列名2)

select 列名1,列名2  from  sql表



实例：

insert into  OPENROWSET('Microsoft.Jet.OLEDB.4.0', 

   'C:\db.mdb';'admin';'', Test) 

select id,name from Test

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'c:\trade.mdb'; 'admin'; '', 表名)

SELECT *

FROM sqltablename





insert into  OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="D:\db.mdb";User ID=Admin;Password=')...Student(studentid,name) select staffid,name from staff

insert into  OPENROWSET('Microsoft.Jet.OLEDB.4.0', 

   'D:\db.mdb';'admin';'', Student) 

select accountid,name from tblbaccount



select * from openrowset('Microsoft.Jet.OLEDB.4.0','D:\db.mdb';'admin';'',Student)

-------------------------------------------------------------------------------------------------



二、SQL SERVER 和EXCEL的数据导入导出

1、在SQL SERVER里查询Excel数据:

-- ======================================================

SELECT * 

FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]

下面是个查询的示例，它通过用于 Jet 的 OLE DB 提供程序查询 Excel 电子表格。

SELECT * 

FROM OpenDataSource ( 'Microsoft.Jet.OLEDB.4.0',

  'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions



SELECT * 

FROM OpenDataSource ( 'Microsoft.Jet.OLEDB.4.0',

  'Data Source="D:\财务统计科目.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...vib

-------------------------------------------------------------------------------------------------



2、将Excel的数据导入SQL server :

-- ======================================================

SELECT * into newtable

FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

  'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]

实例:

SELECT * into newtable

FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

  'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions

-------------------------------------------------------------------------------------------------



3、将SQL SERVER中查询到的数据导成一个Excel文件

-- ======================================================

T-SQL代码：

EXEC master..xp_cmdshell 'bcp 库名.dbo.表名out c:\Temp.xls -c -q -S"servername" -U"sa" -P""'

参数：S 是SQL服务器名；U是用户；P是密码



EXEC master..xp_cmdshell 'bcp axzq.dbo.Staff out d:staff.xls -c -q -S"." -U"sa" -P"gazx"'



说明：还可以导出文本文件等多种格式

实例:EXEC master..xp_cmdshell 'bcp saletesttmp.dbo.CusAccount out c:\temp1.xls -c -q -S"pmserver" -U"sa" -P"sa"'

EXEC master..xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout C:\ authors.xls -c -Sservername -Usa -Ppassword'



EXEC master..xp_cmdshell 'bcp "select name from axzq..staff order by name"  queryout d:staffName.xls -c -q -S"." -U"sa" -P"gazx"'



在VB6中应用ADO导出EXCEL文件代码： 

Dim cn  As New ADODB.Connection

cn.open "Driver={SQL Server};Server=WEBSVR;DataBase=WebMis;UID=sa;WD=123;"

cn.execute "master..xp_cmdshell 'bcp "SELECT col1, col2 FROM 库名.dbo.表名" queryout E:\DT.xls -c -Sservername -Usa -Ppassword'"

------------------------------------------------------------------------------------------------



4、在SQL SERVER里往Excel插入数据:

-- ======================================================

insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

'Data Source="c:\Temp.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...table1 (A1,A2,A3) values (1,2,3)



T-SQL代码：

INSERT INTO  

OPENDATASOURCE('Microsoft.JET.OLEDB.4.0',  

'Extended Properties=Excel 8.0;Data source=C:\training\inventur.xls')...[Filiale1$]  

(bestand, produkt) VALUES (20, 'Test')  

-------------------------------------------------------------------------------------------------



总结：利用以上语句，我们可以方便地将SQL SERVER、ACCESS和EXCEL电子表格软件中的数据进行转换，为我们提供了极大方便！

EXEC   master..xp_cmdshell   'bcp   "select OrderID,ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry from Northwind.dbo.orders"   queryout   "d:\Oreders.txt" -t"|"    -c   -q   -S"127.0.0.1"   -U"sa"   -P""'  

SELECT  *    

  FROM  OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data  Source="xxxx.xls";Extended  Properties="Excel  8.0";IMEX=1;Persist  Security  Info=False')...[a1$] 



二、SQL SERVER 和EXCEL的数据导入导出

1、在SQL SERVER里查询Excel数据:

-- ======================================================

SELECT * 

FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]

下面是个查询的示例，它通过用于 Jet 的 OLE DB 提供程序查询 Excel 电子表格。

SELECT * 

FROM OpenDataSource ( 'Microsoft.Jet.OLEDB.4.0',

  'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions



SELECT * 

FROM OpenDataSource ( 'Microsoft.Jet.OLEDB.4.0',

  'Data Source="d:\staff.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...staff

-------------------------------------------------------------------------------------------------



2、将Excel的数据导入SQL server :

-- ======================================================

SELECT * into newtable

FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

  'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]

实例:

SELECT * into newtable

FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

  'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions

-------------------------------------------------------------------------------------------------



3、将SQL SERVER中查询到的数据导成一个Excel文件

-- ======================================================

T-SQL代码：

EXEC master..xp_cmdshell 'bcp 库名.dbo.表名out c:\Temp.xls -c -q -S"servername" -U"sa" -P""'

参数：S 是SQL服务器名；U是用户；P是密码

说明：还可以导出文本文件等多种格式

实例:EXEC master..xp_cmdshell 'bcp saletesttmp.dbo.CusAccount out c:\temp1.xls -c -q -S"pmserver" -U"sa" -P"sa"'

EXEC master..xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout C:\ authors.xls -c -Sservername -Usa -Ppassword'

在VB6中应用ADO导出EXCEL文件代码： 

Dim cn  As New ADODB.Connection

cn.open "Driver={SQL Server};Server=WEBSVR;DataBase=WebMis;UID=sa;WD=123;"

cn.execute "master..xp_cmdshell 'bcp "SELECT col1, col2 FROM 库名.dbo.表名" queryout E:\DT.xls -c -Sservername -Usa -Ppassword'"

------------------------------------------------------------------------------------------------



4、在SQL SERVER里往Excel插入数据:

-- ======================================================

insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',

'Data Source="c:\Temp.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...table1 (A1,A2,A3) values (1,2,3)



T-SQL代码：

INSERT INTO  

OPENDATASOURCE('Microsoft.JET.OLEDB.4.0',  

'Extended Properties=Excel 8.0;Data source=C:\training\inventur.xls')...[Filiale1$]  

(bestand, produkt) VALUES (20, 'Test')  







--开启xp_cmdshell

--SQL Server blocked access to procedure 'xp_cmdshell'

sp_configure 'show advanced options', 1

go

reconfigure

go

sp_configure 'xp_cmdshell', 1

go

reconfigure

go



--开启sp_OACreate

--SQL Server blocked access to procedure 'sys.sp_OACreate'

sp_configure 'show advanced options', 1;

go

reconfigure;

go

sp_configure 'ole automation procedures', 1;

go

reconfigure;

go





sp_configure 'Ad Hoc Distributed Queries',1;

go

reconfigure

go
ws_hgo 2010-04-25
  • 打赏
  • 举报
 回复 
/*

openDatasource:不使用连接服务器的名称,而提供特殊的链接信息.

使用openDatasource的前提是仅当 DisallowAdhocAccess 

注册表选项针对指定的提供程序显式设置为 0，

并且启用 Ad Hoc Distributed Queries 高级配置选项时，

OPENDATASOURCE 才可用于访问 OLE DB 数据源中的远程数据。

如果未设置这些选项，则默认行为不允许即席访问。

*/

--代码如下:

exec sp_configure 'show advanced options',1

reconfigure

exec sp_configure 'Ad Hoc Distributed Queries',1

reconfigure

--(1)例如现在我想打开D盘中BJData.mdb中的BJAdmin表的信息

select ID,Title,OwnerName from 

opendataSource('Microsoft.Jet.OLEDB.4.0','Data Source="D:\BJData.mdb";')...BJAdmin

/*Results:

ID          Title                OwnerName

----------- -------------------- --------------------------------------------------

1           xinrong              

4           webmaster            admin

5           bing                 webmaster

(3 行受影响)

*/

--(2)我现在要将BJAdmin中BJNewsCategory中的信息插入到A表中

--先查询下Test.Newscategory

select * from  Test.dbo.Newscategory

/*

ID          Title

----------- --------------------

1           MSSQL

(1 行受影响)

*/

insert into Newscategory select Title from OpenDataSource

('Microsoft.Jet.OLEDB.4.0','Data Source="D:\BJData.mdb";')...BJNewsCategory

--导入之后再查询

select * from  Test.dbo.Newscategory

/*

ID          Title

----------- --------------------

1           MSSQL

2           新闻分类

3           新闻动态

4           工程案例

5           膜结构知识

6           在建工程

(6 行受影响)

*/

--上面的都是在MSSQL中查询Access中的信息,现在将MSSQL中的信息导入到Access中去

--(3)将AdminInfo的信息插入到BJData.mdb中的BJAdmin中去

select * from Test.dbo.AdminInfo

/*

ID          Title

----------- ------------------------------

1           ws_hgo

(1 行受影响)

*/

insert into opendatasource('microsoft.Jet.OLEDB.4.0','Data source="D:/BJData.mdb"')...BJAdmin

(Title,RoleID) select Title,1 from AdminInfo

--然后查询BJAdmin表的信息

select ID,Title,OwnerName from 

opendataSource('Microsoft.Jet.OLEDB.4.0','Data Source="D:\BJData.mdb";')...BJAdmin 

/*

ID          Title                OwnerName

----------- -------------------- --------------------------------------------------

1           xinrong              

4           webmaster            admin

5           bing                 webmaster

12          ws_hgo               NULL

(4 行受影响)

*/

--(4)上面说的是Access与MSSQl之间的操作现在操作下

--Excel与MSSQL之间的操作

--从Excel中读取信息

select * from OpenDataSource

('Microsoft.Jet.OLEDB.4.0','Data Source="D:\work.et";Extended properties=Excel 5.0')...[sheet1$]

/*

EmployeeName        Sex            Age             Post

-----------       -----------    --------      ------------ 

ws_hgo             Boy            23             Senior Programmer

yangzhognyuan      Boy            24             Programmer

chenjing           Girl           24             Designer

(3 行受影响)



--(5)

*/

/*将本地表中的信息导入到远程数据库表中

下面的例子将本地whsxjAdmin种的(Title,Password)插入到远程

zhongyiAdmin中去

*/

insert into opendatasource('SQLOLEDB','Data Source=IP地址;User ID=zhongyi;password=xinrongkeji').sqlzhongyi.dbo.zhongyiAdmin(Title,Password)

select Title,Password from whsxjAdmin

--下面说下OPENROWSET

/*

包含访问 OLE DB 数据源中的远程数据所需的全部连接信息。

当访问链接服务器中的表时，这种方法是一种替代方法，

并且是一种使用 OLE DB 连接并访问远程数据的一次性的临时方法。

*/

--(1)直接查询远程服务器上面的信息

select ID,Title from OpenRowset

('SQLOLEDB','IP地址';'zhongyi';'xinrongkeji','select * from sqlzhongyi.dbo.zhongyiAdmin') t

--(2)查询Access里面的信息

select * from OpenRowset

('microsoft.Jet.OLEDB.4.0','d:\BJData.mdb';'Admin';'',BJAdmin) as g







本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/ws_hgo/archive/2010/04/23/5518918.aspx
用sql语句由excel表往sqlserver数据库表中导数据
在查询分析器中，直接用sql语句，把excel表中的数据导入到sqlserver数据库的相应表中
excel导入sql2005的几种方法归纳
近日在巨轮着手车间负荷数据处理，反馈回来的数据是保存在Excel文件中的，我必须将其导入SQL Server2005中，供存储过程计算。 由于之前没有将Excel数据导入SQL Server2005的经验，因此摸索着花了一天时间才搞定。...
7天带你玩转Mysql数据库之SQL语句
您观看课程学习后 免费入群领取【超全Python资料包+17本学习电子书】 七年SQL语句编写经验。满满的干货！ 在网上找了好多SQL数据库的教程...真正做到面对任何问题能够判断出SQL语句是不是能够实现，该怎样实现。 
Excel数据导入SQLServerl数据库
将Excel数据相对应的导入数据库指定数据表内、或者新建表中，省去繁琐的SQL语句操作！ 也可在线下载备份数据库数据
java版Excel文件导入数据库源代码
java版Excel文件导入数据库源代码 servlet容器:tomcat(或者其他) 数据库:oracle(或者其他) 使用框架:jsp+struts1.2 支持字符型/数据型/日期型/CLOB等数据 使用步骤: 1.下载本资源,解压后得到4个文件:...
应用实例

27,579

社区成员

68,558

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章