SQL数据导入导出语句大全[英文部分比较全面]

简介：微软SQL Server数据库SQL语句导入导出大全，包括与其他数据库和文件的数据的导入导出。

/*******  导出到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

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

/** 导入文本文件
EXEC master..xp_cmdshell ’bcp "dbname..tablename" in c:\DT.txt -c -Sservername -Usa -Ppassword’

/** 导出文本文件
EXEC master..xp_cmdshell ’bcp "dbname..tablename" out c:\DT.txt -c -Sservername -Usa -Ppassword’
或
EXEC master..xp_cmdshell ’bcp "Select * from dbname..tablename" queryout c:\DT.txt -c -Sservername -Usa -Ppassword’

导出到TXT文本，用逗号分开
exec master..xp_cmdshell ’bcp "库名..表名" out "d:\tt.txt" -c -t ,-U sa -P password’

BULK INSERT 库名..表名
FROM ’c:\test.txt’
WITH (
    FIELDTERMINATOR = ’;’,
    ROWTERMINATOR = ’\n’
)

--/* dBase IV文件
select * from
OPENROWSET(’MICROSOFT.JET.OLEDB.4.0’
,’dBase IV;HDR=NO;IMEX=2;DATABASE=C:\’,’select * from [客户资料4.dbf]’)
--*/

--/* dBase III文件
select * from
OPENROWSET(’MICROSOFT.JET.OLEDB.4.0’
,’dBase III;HDR=NO;IMEX=2;DATABASE=C:\’,’select * from [客户资料3.dbf]’)
--*/

--/* FoxPro 数据库
select * from openrowset(’MSDASQL’,
’Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:\’,
’select * from [aa.DBF]’)
--*/

/**************导入DBF文件****************/
select * from openrowset(’MSDASQL’,
’Driver=Microsoft Visual FoxPro Driver;
SourceDB=e:\VFP98\data;
SourceType=DBF’,
’select * from customer where country != "USA" order by country’)
go
/***************** 导出到DBF ***************/
如果要导出数据到已经生成结构(即现存的)FOXPRO表中,可以直接用下面的SQL语句

insert into openrowset(’MSDASQL’,
’Driver=Microsoft Visual FoxPro Driver;SourceType=DBF;SourceDB=c:\’,
’select * from [aa.DBF]’)
select * from 表

说明:

SourceDB=c:\  指定foxpro表所在的文件夹
aa.DBF        指定foxpro表的文件名.

/*************导出到Access********************/
insert into openrowset(’Microsoft.Jet.OLEDB.4.0’,
   ’x:\A.mdb’;’admin’;’’,A表) select * from 数据库名..B表

/*************导入Access********************/
insert into B表 selet * from openrowset(’Microsoft.Jet.OLEDB.4.0’,
   ’x:\A.mdb’;’admin’;’’,A表)

 

 

*********************  导入 xml　文件

DECLARE @idoc int
DECLARE @doc varchar(1000)
--sample XML document
SET @doc =’
<root>
  <Customer cid= "C1" name="Janine" city="Issaquah">
      <Order oid="O1" date="1/20/1996" amount="3.5" />
      <Order oid="O2" date="4/30/1997" amount="13.4">Customer was very satisfied
      </Order>
   </Customer>
   <Customer cid="C2" name="Ursula" city="Oelde" >
      <Order oid="O3" date="7/14/1999" amount="100" note="Wrap it blue
             white red">
            <Urgency>Important</Urgency>
            Happy Customer.
      </Order>
      <Order oid="O4" date="1/20/1996" amount="10000"/>
   </Customer>
</root>
’
-- Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

-- Execute a SELECT statement using OPENXML rowset provider.
SELECT *
FROM OPENXML (@idoc, ’/root/Customer/Order’, 1)
      WITH (oid     char(5),
            amount  float,
            comment ntext ’text()’)
EXEC sp_xml_removedocument @idoc

/********************导整个数据库*********************************************/

用bcp实现的存储过程 //from www.w3sky.com

/*
 实现数据导入/导出的存储过程
         根据不同的参数,可以实现导入/导出整个数据库/单个表
 调用示例:
--导出调用示例
----导出单个表
exec file2table ’zj’,’’,’’,’xzkh_sa..地区资料’,’c:\zj.txt’,1
----导出整个数据库
exec file2table ’zj’,’’,’’,’xzkh_sa’,’C:\docman’,1

--导入调用示例
----导入单个表
exec file2table ’zj’,’’,’’,’xzkh_sa..地区资料’,’c:\zj.txt’,0
----导入整个数据库
exec file2table ’zj’,’’,’’,’xzkh_sa’,’C:\docman’,0

*/
if exists(select 1 from sysobjects where name=’File2Table’ and objectproperty(id,’IsProcedure’)=1)
 drop procedure File2Table
go
create procedure File2Table
@servername varchar(200)  --服务器名
,@username varchar(200)   --用户名,如果用NT验证方式,则为空’’
,@password varchar(200)   --密码
,@tbname varchar(500)   --数据库.dbo.表名,如果不指定:.dbo.表名,则导出数据库的所有用户表
,@filename varchar(1000)  --导入/导出路径/文件名,如果@tbname参数指明是导出整个数据库,则这个参数是文件存放路径,文件名自动用表名.txt
,@isout bit      --1为导出,0为导入 //from www.w3sky.com
as
declare @sql varchar(8000)

if @tbname like ’%.%.%’ --如果指定了表名,则直接导出单个表
begin
 set @sql=’bcp ’+@tbname
  +case when @isout=1 then ’ out ’ else ’ in ’ end
  +’ "’+@filename+’" /w’
  +’ /S ’+@servername
  +case when isnull(@username,’’)=’’ then ’’ else ’ /U ’+@username end
  +’ /P ’+isnull(@password,’’)
 exec master..xp_cmdshell @sql
end
else
begin --导出整个数据库,定义游标,取出所有的用户表
 declare @m_tbname varchar(250)
 if right(@filename,1)<>’\’ set @filename=@filename+’\’

 set @m_tbname=’declare #tb cursor for select name from ’+@tbname+’..sysobjects where xtype=’’U’’’
 exec(@m_tbname)
 open #tb
 fetch next from #tb into @m_tbname
 while @@fetch_status=0
 begin
  set @sql=’bcp ’+@tbname+’..’+@m_tbname
   +case when @isout=1 then ’ out ’ else ’ in ’ end
   +’ "’+@filename+@m_tbname+’.txt " /w’
   +’ /S ’+@servername
   +case when isnull(@username,’’)=’’ then ’’ else ’ /U ’+@username end
   +’ /P ’+isnull(@password,’’)
  exec master..xp_cmdshell @sql
  fetch next from #tb into @m_tbname
 end
 close #tb
 deallocate #tb 
end
go

 

/**********************Excel导到Txt****************************************/
想用
select * into opendatasource(...) from opendatasource(...)
实现将一个Excel文件内容导入到一个文本文件

假设Excel中有两列，第一列为姓名，第二列为很行帐号(16位)
且银行帐号导出到文本文件后分两部分，前8位和后8位分开。

如果要用你上面的语句插入的话,文本文件必须存在,而且有一行:姓名,银行账号1,银行账号2
然后就可以用下面的语句进行插入
注意文件名和目录根据你的实际情况进行修改.

insert into
opendatasource(’MICROSOFT.JET.OLEDB.4.0’
,’Text;HDR=Yes;DATABASE=C:\’
)...[aa#txt]
--,aa#txt)
--*/
select 姓名,银行账号1=left(银行账号,8),银行账号2=right(银行账号,8)
from
opendatasource(’MICROSOFT.JET.OLEDB.4.0’
,’Excel 5.0;HDR=YES;IMEX=2;DATABASE=c:\a.xls’
--,Sheet1$)
)...[Sheet1$]

如果你想直接插入并生成文本文件,就要用bcp

declare @sql varchar(8000),@tbname varchar(50)

--首先将excel表内容导入到一个全局临时表
select @tbname=’[##temp’+cast(newid() as varchar(40))+’]’
 ,@sql=’select 姓名,银行账号1=left(银行账号,8),银行账号2=right(银行账号,8)
into ’+@tbname+’ from
opendatasource(’’MICROSOFT.JET.OLEDB.4.0’’
,’’Excel 5.0;HDR=YES;IMEX=2;DATABASE=c:\a.xls’’
)...[Sheet1$]’
exec(@sql)

--然后用bcp从全局临时表导出到文本文件
set @sql=’bcp "’+@tbname+’" out "c:\aa.txt" /S"(local)" /P"" /c’
exec master..xp_cmdshell @sql

--删除临时表
exec(’drop table ’+@tbname)

用bcp将文件导入导出到数据库的存储过程:

/*--bcp-二进制文件的导入导出

 支持image,text,ntext字段的导入/导出
 image适合于二进制文件;text,ntext适合于文本数据文件

 注意:导入时,将覆盖满足条件的所有行
  导出时,将把所有满足条件的行也出到指定文件中

 此存储过程仅用bcp实现
邹建 2003.08-----------------*/

/*--调用示例
--数据导出
 exec p_binaryIO ’zj’,’’,’’,’acc_演示数据..tb’,’img’,’c:\zj1.dat’

--数据导出
 exec p_binaryIO ’zj’,’’,’’,’acc_演示数据..tb’,’img’,’c:\zj1.dat’,’’,0
--*/
if exists (select * from dbo.sysobjects where id = object_id(N’[dbo].[p_binaryIO]’) and OBJECTPROPERTY(id, N’IsProcedure’) = 1)
drop procedure [dbo].[p_binaryIO]
GO

Create proc p_binaryIO
@servename varchar (30),--服务器名称
@username varchar (30), --用户名
@password varchar (30), --密码
@tbname varchar (500),  --数据库..表名
@fdname varchar (30),  --字段名
@fname varchar (1000), --目录+文件名,处理过程中要使用/覆盖:@filename+.bak
@tj varchar (1000)=’’,  --处理条件.对于数据导入,如果条件中包含@fdname,请指定表名前缀
@isout bit=1   --1导出((默认),0导入
AS
declare @fname_in varchar(1000) --bcp处理应答文件名
 ,@fsize varchar(20)   --要处理的文件的大小
 ,@m_tbname varchar(50)  --临时表名
 ,@sql varchar(8000)

--则取得导入文件的大小
if @isout=1
 set @fsize=’0’
else
begin
 create table #tb(可选名 varchar(20),大小 int
  ,创建日期 varchar(10),创建时间 varchar(20)
  ,上次写操作日期 varchar(10),上次写操作时间 varchar(20)
  ,上次访问日期 varchar(10),上次访问时间 varchar(20),特性 int)
 insert into #tb
 exec master..xp_getfiledetails @fname
 select @fsize=大小 from #tb
 drop table #tb
 if @fsize is null
 begin
  print ’文件未找到’
  return
 end

end

 

--生成数据处理应答文件
set @m_tbname=’[##temp’+cast(newid() as varchar(40))+’]’
set @sql=’select * into ’+@m_tbname+’ from(
 select null as 类型
 union all select 0 as 前缀
 union all select ’+@fsize+’ as 长度
 union all select null as 结束
 union all select null as 格式
 ) a’
exec(@sql)
select @fname_in=@fname+’_temp’
 ,@sql=’bcp "’+@m_tbname+’" out "’+@fname_in
 +’" /S"’+@servename
 +case when isnull(@username,’’)=’’ then ’’
  else ’" /U"’+@username end
 +’" /P"’+isnull(@password,’’)+’" /c’
exec master..xp_cmdshell @sql
--删除临时表
set @sql=’drop table ’+@m_tbname
exec(@sql)

if @isout=1
begin
 set @sql=’bcp "select top 1 ’+@fdname+’ from ’
  +@tbname+case isnull(@tj,’’) when ’’ then ’’
   else ’ where ’+@tj end
  +’" queryout "’+@fname
  +’" /S"’+@servename
  +case when isnull(@username,’’)=’’ then ’’
   else ’" /U"’+@username end
  +’" /P"’+isnull(@password,’’)
  +’" /i"’+@fname_in+’"’
 exec master..xp_cmdshell @sql
end
else
begin
 --为数据导入准备临时表
 set @sql=’select top 0 ’+@fdname+’ into ’
  +@m_tbname+’ from ’ +@tbname
 exec(@sql)

 --将数据导入到临时表
 set @sql=’bcp "’+@m_tbname+’" in "’+@fname
  +’" /S"’+@servename
  +case when isnull(@username,’’)=’’ then ’’
   else ’" /U"’+@username end
  +’" /P"’+isnull(@password,’’)
  +’" /i"’+@fname_in+’"’
 exec master..xp_cmdshell @sql
 
 --将数据导入到正式表中
 set @sql=’update ’+@tbname
  +’ set ’+@fdname+’=b.’+@fdname
  +’ from ’+@tbname+’ a,’
  +@m_tbname+’ b’
  +case isnull(@tj,’’) when ’’ then ’’
   else ’ where ’+@tj end
 exec(@sql)

 --删除数据处理临时表
 set @sql=’drop table ’+@m_tbname
end

--删除数据处理应答文件
set @sql=’del ’+@fname_in
exec master..xp_cmdshell @sql

go

/** 导入文本文件
EXEC master..xp_cmdshell ’bcp "dbname..tablename" in c:\DT.txt -c -Sservername -Usa -Ppassword’

改为如下，不需引号
EXEC master..xp_cmdshell ’bcp dbname..tablename in c:\DT.txt -c -Sservername -Usa -Ppassword’

/** 导出文本文件
EXEC master..xp_cmdshell ’bcp "dbname..tablename" out c:\DT.txt -c -Sservername -Usa -Ppassword’
此句需加引号

Datenimport/-export - Werkzeuge

Ad-hoc Abfragen mit OPENROWSET und OPENDATASOURCE

[Achtung. Diese Seite wird zur Zeit noch bearbeitet...]

Ad-hoc Abfragen sind ein flexibles Mittel, um schnell innerhalb von Transact-SQL auf Daten einer fremden Datenquelle zuzugreifen. Da bei diesem Aufruf die Verbindungsoptionen (insbesondere der Sicherheitskontext) dynamisch angegeben wird, sollte in jedem Fall geprüft werden, ob solche Abfragen überhaupt zugelassen werden sollten (s.dazu auch: OPENROWSET-Problem in SQL Server 7.0).

OPENROWSET

Mit Hilfe der Funktion OPENROWSET kann ein Rowset von einer OLEDB-Datenquelle geöffnet werden. Diese Funktion benötigt als Parameter den zu verwendenden OLEDB-Provider und alle weiteren notwendigen Verbindungseigenschaften incl. des Sicherheitskontexts. Als letzter Parameter wird die auszuführende SELECT-Anweisung angegeben.

Syntax:

Die Grundsyntax für OPENROWSET sieht so aus:

OPENROWSET ('name_des_OLEDB_providers' , 'verbindungszeichenfolge', 'abfrage')

Die Parameter

name_des_OLEDB_providers

Der Name des OLEDB-Providers, z.B. SQLOLEDB für Zugriffe auf SQL Server, Microsoft.Jet.OLEDB.4.0 für den Zugriff auf Access-Datenbanken, Excel-Arbeitsmappen, Textdateien, dBase-Dateien etc.)

verbindungszeichenfolge

Die Verbindungszeichenfolge für den OLEDB-Provider. Die hier anzugebende Zeichenfolge wird vom SQL Server intern interpretiert und entspricht (leider) nicht genau dem "gewohnten" Aufbau.

abfrage

Die Abfrage auf die Daten in der fernen Datenquelle. In der Regel ein SELECT auf die gewünschte Tabelle

Beispiele SQL Server/SQLOLEDB

Zugriff auf einen anderen SQL Server mit vertrauter Sicherheit

-- Select auf anderen SQL Server mit vertrauter Verbindung   
 SELECT * FROM OPENROWSET  
 ('SQLOLEDB',   
  'Server=anderer_server;database=Northwind;TRUSTED_CONNECTION=YES;',   
  'SELECT * FROM northwind.dbo.orders')   
   
 

Vorsicht Falle!

Das folgende Beispiel gibt Server und Datenbank über die OLEDB-Properties an; diese Parameter werden jedoch ignoriert

-- vorsicht: Hier werden Data Source und Initial Catalog ignoriert...  
 -- stattdessen wird eine Verbindung zum lokalen Server aufgebaut...  
 SELECT * FROM  
 OPENROWSET('SQLOLEDB',   
 'Data Source=anderer_Server;Initial Catalog=beispiel5;TRUSTED_CONNECTION=YES;',   
 'SELECT @@servername, * FROM northwind.dbo.orders') 

Update auf Daten eines anderen SQL Servers

Vorsicht! Diese Anweisung ändert Daten auf dem angegebenen Server.

UPDATE  
 OPENROWSET('SQLOLEDB',   
 'Server=anderer_server;Database=Northwind;TRUSTED_CONNECTION=YES;',   
 'SELECT * FROM northwind.dbo.orders')   
 SET orderdate = orderdate + 1  
 

Einfügen neuer Datensätze in einer "entfernten" Tabelle

Vorsicht! Diese Anweisung ändert Daten auf dem angegebenen Server.

INSERT INTO  
 OPENROWSET('SQLOLEDB',   
 'Server=anderer_server;Database=Northwind;TRUSTED_CONNECTION=YES;',   
 'SELECT * FROM northwind.dbo.region')   
 VALUES (99, 'Test')  
 

Löschen von Daten in einer Tabelle

Vorsicht! Diese Anweisung ändert Daten auf dem angegebenen Server.

DELETE FROM  
 OPENROWSET('SQLOLEDB',   
 'Server=anderer_server;Database=Northwind;TRUSTED_CONNECTION=YES;',   
 'SELECT * FROM northwind.dbo.region')   
 WHERE RegionID = 99  
 

Beispiele Access Datenbanken/JET

Zugriff auf Daten in einer Access-Datenbank mit SELECT

SELECT * FROM  
 OPENROWSET('Microsoft.Jet.OLEDB.4.0',  
 ';Database=C:\Access-Datenbanken\nordwind.mdb',  
 'SELECT * FROM artikel')

Zugriff auf Daten in einer kennwortgeschützten Access-Datenbank mit SELECT

-- Select mit Kennwortgeschützter Datenbank  
 SELECT * FROM  
 OPENROWSET('Microsoft.Jet.OLEDB.4.0',  
 ';Database=C:\Access-Datenbanken\vertraulich.mdb;pwd=geheim;',  
 'SELECT * FROM umsatz')   
 GO

Aktualisieren von Daten in einer Jet-Datenbank

Vorsicht! Diese Anweisung ändert Daten in der angegebenen Datenbank.

UPDATE  
 OPENROWSET('Microsoft.Jet.OLEDB.4.0',  
 ';Database=C:\Access-Datenbanken\nordwind.mdb',  
 'SELECT * FROM artikel')  
 SET einzelpreis = einzelpreis + 1  
 GO

Beispiele Excel-Arbeitsmappen/JET

Abfragen eines Arbeitsblatts

SELECT * FROM   
 OPENROWSET('Microsoft.JET.OLEDB.4.0',  
 'Excel 8.0;Database=C:\training\inventur.xls',  
 'SELECT * FROM [Filiale1$]')

Abfragen bestimmter Zellen in einem Arbeitsblatt

SELECT * FROM   
 OPENROWSET('Microsoft.JET.OLEDB.4.0',  
 'Excel 8.0;HDR=NO;Database=C:\training\inventur.xls',  
 'SELECT * FROM [Filiale1$A2:B4]')

Aktualisieren von Zellen in einem Arbeitsblatt

Vorsicht! Dieses Beispiel ändert Daten im angegebenen Excel-Arbeitsblatt

UPDATE  
 OPENROWSET('Microsoft.JET.OLEDB.4.0',  
 'Excel 8.0;Database=C:\training\inventur.xls',  
 'SELECT * FROM [Filiale1$]')  
 SET Bestand = 1 WHERE Bestand = 2

Hinzufügen von Daten in ein Excel-Arbeitsblatt

Vorsicht! Dieses Beispiel ändert Daten im angegebenen Excel-Arbeitsblatt

INSERT INTO  
 OPENROWSET('Microsoft.JET.OLEDB.4.0',  
 'Excel 8.0;Database=C:\training\inventur.xls',  
 'SELECT * FROM [Filiale1$]')  
 (Produkt, Bestand) VALUES ('Test', 27)  
 GO

Beispiele Textdateien/JET

Abfragen von Daten aus einer Textdatei

-- Abfragen einer Textdatei  
 SELECT * FROM   
 OPENROWSET('MICROSOFT.JET.OLEDB.4.0',  
 'Text;Database=C:\;',   
 'SELECT * FROM [orders#txt]')  
 /* Inhalt der Datei SCHEMA.INI auf C:\  
 [orders.txt]  
 Format=TABDelimited  
 ColNameHeader=false  
 MaxScanRows=20  
 */

Anfügen eines Satzes in eine Textdatei

-- Einfügen eines neuen Satzes  
 INSERT INTO  
 OPENROWSET('MICROSOFT.JET.OLEDB.4.0',  
 'Text;Database=C:\;',   
 'SELECT * FROM [textdemo#txt]')  
 VALUES ('XXX', 100)  
 -- Prüfen, ob der neue Satz angefügt wurde:  
 SELECT * FROM  
 OPENROWSET('MICROSOFT.JET.OLEDB.4.0',  
 'Text;Database=C:\;',   
 'SELECT * FROM [textdemo#txt]')  
   
 /* Zugehörige Schema.ini Datei  
 [textdemo.txt]  
 CharacterSet=ANSI  
 Format=TABDelimited  
 ColNameHeader=TRUE  
 MaxScanRows=20  
 */  
 GO

Beispiele HTML-Dateien/Jet

Lesen der Daten aus einer HTML-Tabelle mit Caption

SELECT * FROM  
 OPENROWSET('Microsoft.Jet.OLEDB.4.0',   
 'HTML Import;HDR=NO;Database=http://localhost/beispiele/tablemitcaption.htm;',   
 'SELECT * FROM Beispieltabelle')  
 

OPENDATASOURCE

OPENDATASOURCE wurde im SQL Server 2000 eingeführt. Diese Funktion erlaubt die Verwendung einer OLEDB-Datenquelle in einem vierstelligem Namen. Im Gegensatz zu OPENROWSET werden hier die "echten" OLEDB-Provider Eigenschaften verwendet.

Syntax

OPENDATASOURCE ( provider, provider_zeichenfolge )

Parameter

Beispiele SQL Server/SQLOLEDB

Lesender Zugriff auf Daten eines anderen Servers

-- Integrierte Sicherheit  
 SELECT * FROM   
 OPENDATASOURCE('SQLOLEDB',   
 'Data Source=itrainbo2000;Initial Catalog=Northwind;Integrated Security=SSPI;').Northwind.dbo.orders  
 -- SQL Login  
 SELECT * FROM   
 OPENDATASOURCE('SQLOLEDB',   
 'Data Source=itrainbo2000;Initial Catalog=Northwind;User ID=student;Password=#student#;').Northwind.dbo.orders  
 

Aufruf einer gespeicherten Prozedur mit Parameter auf einem entfernten SQL Server

EXEC   
 OPENDATASOURCE('SQLOLEDB',   
 'Data Source=itrainbo2000;Initial Catalog=Northwind;Integrated Security=SSPI;').Northwind.dbo.CustorderHist 'ALFKI'  
 

Beispiele Access/JET

SELECT auf eine Tabelle in einer Access-Datenbank

SELECT * FROM   
 OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',   
 'Data Source=C:\Access-Datenbanken\nordwind.mdb;')...artikel

SELECT auf eine kennwortgeschützte Tabelle in einer Access-Datenbank

SELECT * FROM   
 OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',   
 'Data Source=C:\Access-Datenbanken\umsatz.mdb;Jet OLEDB:Database Password=geheim')...tabzahlen  
 

Beispiele Excel-Arbeitsmappe/JET

Abfragen des gesamten Inhalts eines Arbeitsblatts

SELECT * FROM   
 OPENDATASOURCE('Microsoft.JET.OLEDB.4.0',  
 'Extended Properties=Excel 8.0;Data source=C:\training\inventur.xls')...[Filiale1$]

Der Zugriff auf einzelne Bereiche innerhalb des Arbeitsblatt über Zellenreferenzen ist in Kombination mit OPENDATASOURCE so nicht möglich:

-- Das geht nicht:  
 SELECT * FROM   
 OPENDATASOURCE('Microsoft.JET.OLEDB.4.0',  
 'Extended Properties=Excel 8.0;Data source=C:\training\inventur.xls')...[Filiale1$A1:B3]  
 

Aktualisieren von Werten in einem Excel-Arbeitsblatt

Vorsicht! Dieses Beispiel verändert Daten in dem angegebenen Arbeitsblatt

UPDATE   
 OPENDATASOURCE('Microsoft.JET.OLEDB.4.0',  
 'Extended Properties=Excel 8.0;Data source=C:\training\inventur.xls')...[Filiale1$]  
 SET bestand = bestand + 1

Einfügen einer Zeile in ein Excel-Arbeitsblatt

Vorsicht! Dieses Beispiel verändert Daten in dem angegebenen Arbeitsblatt

INSERT INTO  
 OPENDATASOURCE('Microsoft.JET.OLEDB.4.0',  
 'Extended Properties=Excel 8.0;Data source=C:\training\inventur.xls')...[Filiale1$]  
 (bestand, produkt) VALUES (100, 'Test')  
 

Einfügen von Daten aus einer SQL Server Tabelle in eine Excel-Arbeitsmappe

INSERT INTO   
 OPENDATASOURCE('Microsoft.JET.OLEDB.4.0',  
 'Extended Properties=Excel 8.0;Data source=C:\training\inventur.xls')...[Filiale1$]  
 (Produkt, Bestand)  
 SELECT ProductName, 0 FROM northwind.dbo.products

Beispiele HTML-Dateien/JET

Abfrage einer HTML-Tabelle mit Caption und Spaltenüberschriften

SELECT * FROM   
 OPENDATASOURCE('Microsoft.JET.OLEDB.4.0',  
 'Extended Properties=HTML Import;Data Source=http://localhost/beispiele/tablemitcaption.htm;')...Beispieltabelle  
 

Erlaubte Operationen nach Datenquelle

Provider	SELECT	INSERT	UPDATE	DELETE
SQLOLEDB	ja	ja	ja	ja
Jet/Access	ja	ja	ja	ja
Jet/Excel	ja	ja	ja	nein
Jet/Text	ja	ja	nein	nein
Jet/Lotus Worksheet	ja	ja	ja	nein
Jet/HTML	ja	nein	nein	nein
Jet/dBase	ja	ja	ja	ja
Jet/Paradox	ja	ja	ja	ja

Typische Fehlermeldungen in Zusammenhang mit OPENROWSET

[OLE/DB provider returned message: Deferred prepare could not be completed.]

Sie haben beim Zugriff auf die Tabelle nicht den voll-qualifierten Namen (datenbank.besitzer.tabelle) angegeben.

Der Ad-hoc-Zugriff auf den OLE DB-Provider 'Microsoft.Jet.OLEDB.4.0' wurde verweigert. Sie müssen auf diesen Provider über einen Verbindungsserver zugreifen.

Der Ad-hoc Zugriff auf den OLEDB-Provider Jet.OLEDB wurde aus Sicherheitsgründen deaktiviert. Greifen Sie entweder über einen Verbindungsserver auf die Daten zu oder aktivieren Sie Adhoc-Zugriffe für den JET-OLEDB Provider (Achtung! Dies beeinträchtigt die Sicherheit des Servers.)

So prüfen Sie, ob Ad-Hoc Abfragen zugelassen sind:

1. Öffnen Sie im Enterprise Manager den Unterpunkt Sicherheit.
2. Öffnen Sie das Kontextmenü des Unterpunkts "Verbindungsserver" und wählen Sie den Menüpunkt "Neuer Verbindungsserver" aus.
3. Wählen Sie in der Auswahlliste "Providername" den Provider "Microsoft.Jet.OLEDB.4.0" aus.
4. Klicken Sie auf die Schaltfläche "Provideroptionen".
5. Prüfen Sie in der Liste "Provideroptionen" den Wert für die Option "Ad-hoc Zugriffe nicht zulassen".

Die folgende Anweisung können Sie verwenden, um per Transact-SQL Script zu prüfen, ob Ad-hoc Zugriffe für den Microsoft Jet.4.0.OLEDB Provider zugelassen sind:

EXEC master..xp_instance_regread
        N'HKEY_LOCAL_MACHINE',
        N'SOFTWARE\Microsoft\MSSQLServer\Providers\Microsoft.Jet.OLEDB.4.0',
        N'DisallowAdhocAccess'

Der OLE DB-Provider 'Microsoft.JET.OLEDB.4.0' meldete einen Fehler.
[OLE/DB provider returned message: Installierbares ISAM nicht gefunden.]

Prüfen Sie, ob der angegebene ISAM-Treiber auf dem Server verfügbar ist. In vielen Fällen führt auch eine fehlerhafte Angabe in der Verbindungszeichenfolge zu dieser Fehlermeldung.