sql语句完成数据的导⼊导出
熟悉SQL SERVER的数据库管理员都知道，其DTS可以进⾏数据的导⼊导出，其实，我们也可以使⽤Transact-SQL语句进⾏导⼊导出操作。在 Transact-SQL语句中，我们主要使⽤OpenDataSource函数、OPENROWSET 函数，关于函数的详细说明，请参考SQL联机帮助。利⽤下述⽅法，可以⼗分容易地实现SQL SERVER、ACCESS、EXCEL数据转换，详细说明如下：
⼀、SQL SERVER 和ACCESS的数据导⼊导出
常规的数据导⼊导出：
使⽤DTS向导迁移你的Access数据到SQL Server，你可以使⽤这些步骤:
○1在SQL SERVER企业管理器中的Tools（⼯具）菜单上，选择Data Transformation
○2Services（数据转换服务），然后选择 czdImport Data（导⼊数据）。
○3在Choose a Data Source（选择数据源）对话框中选择Microsoft Access as the Source，然后键⼊你的.mdb数据库(.mdb⽂件扩展名)的⽂件名或通过浏览寻该⽂件。○4在Choose a Destination（选择⽬标）对话框中，选择Microsoft OLE　DB Prov ider for SQL　Server，选择数据库服务器，然后单击必要的验证⽅式。
○5在Specify Table Copy（指定表格复制）或Query（查询）对话框中，单击Copy tables（复制表格）。
○6在Select Source Tables（选择源表格）对话框中，单击Select All（全部选定）。下⼀步，完成。
Transact-SQL语句进⾏导⼊导出：
1.在SQL SERVER⾥查询access数据:
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:"DB.mdb";User ID=Admin;Password=')...表名
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
⼆、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
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⽂件
增加字段的sql语句
T-SQL代码：
p_cmdshell 'bcp 库名.dbo.表名out c:"Temp.xls -c -q -S"servername" -U"sa" -P""'
参数：S 是SQL服务器名;U是⽤户;P是密码
说明：还可以导出⽂本⽂件等多种格式
实例:p_cmdshell 'bcp saletesttmp.dbo.CusAccount out c:"temp1.xls -c -q -S"pmserver" -U"sa" -P"sa"'
p_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;"
路径需要多写层，即 e:\test\aa.xls⽽不是e:\aa.xls
查看有没有权限
EXEC xp_cmdshell hi! > E:\hi.txt' 总是拒绝访问
EXEC xp_cmdshell hi! > E:\' 创建了Tests⽂件夹即可写⼊
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电⼦表格软件中的数据进⾏转换，为我们提供了极⼤⽅便！
问题1：
SQL Server 阻⽌了对组件 'Ad Hoc Distributed Queries' 的 STATEMENT'OpenRowset/OpenDatasource' 的访问，因为此组件已作为此服务器安全配置的⼀部分⽽被关闭。系统管理员可以通过使⽤ sp_configure 启⽤ 'Ad Hoc Distributed Queries'。有关启⽤ 'Ad Hoc Distributed Queries' 的详细信息，请参阅 SQL Server 联机丛书中的 "外围应⽤配置器"。
查询相关资料，到解决⽅法：
启⽤Ad Hoc Distributed Queries：
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
使⽤完成后，关闭Ad Hoc Distributed Queries：
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure
问题2：
EXCEL导⼊到SQL Server经常出现“⽂本被截断，或者⼀个或多个字符在⽬标代码页中没有匹配项”错误的解决
2011/08/05 17:55
原因是，SQL Server的导⼊导出为了确定数据表的字段类型，取excel⽂件的前8⾏来判别。如果前8条是少于255的字符串，则设成nvarchar(255)类型，但如果Excel后⾯的记录中有长度超过255的，导⼊时就会出错。即使在导⼊设置映射时调整了该字段的长度也不⾏。解决的办法是改注册表
如果是:Jet引擎.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel
如果是:ACE引擎.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel
然后到 TypeGuessRows这个项.默认为8的.修改为0.

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。