/******* Nach Excel exportieren
exec master..xp_cmdshell 'bcp Settleb.dbo.shanghu out c:temp1.xls -c -q -s"gnetdata/gnetdata" -u"sa" -p""'
/*********** Excel importieren
wählen *
von opendatasource( 'microsoft.jet.oledb.4.0',
'data source="c:test.xls";user id=admin;password=;extended Properties=excel 5.0')...xactions
select cast(cast(account number as numeric(10,2)) as nvarchar(255))+' ' konvertierter Alias
von opendatasource( 'microsoft.jet.oledb.4.0',
'data source="c:test.xls";user id=admin;password=;extended Properties=excel 5.0')...xactions
/** Textdatei importieren
exec master..xp_cmdshell 'bcp dbname..tablename in c:dt.txt -c -sservername -usa -ppassword'
/** Textdatei exportieren
exec master..xp_cmdshell 'bcp "dbname..tablename" out c:dt.txt -c -sservername -usa -ppassword'
Dieser Satz muss in Anführungszeichen gesetzt werden
oder
exec master..xp_cmdshell 'bcp "select * from dbname..tablename" queryout c:dt.txt -c -sservername -usa -ppassword'
Export in TXT-Text, durch Kommas getrennt
exec master..xp_cmdshell 'bcp "Bibliotheksname..Tabellenname" out "d:tt.txt" -c -t ,-u sa -p Passwort'
Masseneinfügung Bibliotheksname..Tabellenname
aus 'c:test.txt'
mit (
fieldterminator = ';',
rowterminator = 'n'
)
--/* dbase iv-Datei
Wählen Sie * aus
openrowset('microsoft.jet.oledb.4.0'
,'dbase iv;hdr=no;imex=2;database=c:','select * from [Customer Data 4.dbf]')
--*/
--/* dbase iii-Datei
Wählen Sie * aus
openrowset('microsoft.jet.oledb.4.0'
,'dbase iii;hdr=no;imex=2;database=c:','select * from [Customer Data 3.dbf]')
--*/
--/* foxpro-Datenbank
Wählen Sie * aus openrowset('msdasql',
'driver=Microsoft Visual FoxPro Driver;sourcetype=dbf;sourcedb=c:',
'select * from [aa.dbf]')
--*/
/******************DBF-Datei importieren******************/
Wählen Sie * aus openrowset('msdasql',
'Treiber=Microsoft Visual FoxPro-Treiber;
sourcedb=e:vfp98data;
sourcetype=dbf',
'select * from customer where land != "usa" sortieren nach Land')
gehen
/**************************** Export nach dbf ***********/
Wenn Sie Daten in die generierte Struktur (d. h. vorhandene) Foxpro-Tabelle exportieren möchten, können Sie direkt die folgende SQL-Anweisung verwenden
Einfügen in openrowset('msdasql',
'driver=Microsoft Visual FoxPro-Treiber;sourcetype=dbf;sourcedb=c:',
'select * from [aa.dbf]')
Wählen Sie * aus der Tabelle aus
veranschaulichen:
sourcedb=c: gibt den Ordner an, in dem sich die Foxpro-Tabelle befindet
aa.dbf gibt den Dateinamen der Foxpro-Tabelle an.
/******************Exportieren, um darauf zuzugreifen*************************/
Einfügen in openrowset('microsoft.jet.oledb.4.0',
'x:a.mdb';'admin';'',a Tabelle) Wählen Sie * aus Datenbankname..b Tabelle aus
/******************Zugriff importieren************************/
Einfügen in b-Tabelle selet * from openrowset('microsoft.jet.oledb.4.0',
'x:a.mdb';'admin';'',eine Tabelle)
********************* XML-Datei importieren
deklariere @idoc int
deklariere @doc varchar(1000)
--sample XML-Dokument
set @doc ='
<root>
<Kunde cid= "c1" name="janine" city="issaquah">
<order oid="o1" date="20.01.1996" amount="3.5" />
<order oid="o2" date="30.4.1997" amount="13.4">Kunde war sehr zufrieden
</order>
</Kunde>
<Kunde cid="c2" name="ursula" city="oelde" >
<order oid="o3" date="14.7.1999" amount="100" note="wrap it blue
weiß rot">
<Dringlichkeit>wichtig</Dringlichkeit>
zufriedener Kunde.
</order>
<order oid="o4" date="20.01.1996" amount="10000"/>
</Kunde>
</root>
'
– Erstellen Sie eine interne Darstellung des XML-Dokuments.
exec sp_xml_preparedocument @idoc-Ausgabe, @doc
– Führen Sie eine Select-Anweisung mit dem OpenXML-Rowset-Anbieter aus.
wählen *
von openxml (@idoc, '/root/customer/order', 1)
mit (oid char(5),
Betrag float,
Kommentar ntext 'text()')
exec sp_xml_removedocument @idoc
/*************************Importieren Sie die gesamte Datenbank*********************** ***** *********************/
Mit bcp implementierte gespeicherte Prozedur
/*
Implementieren Sie gespeicherte Prozeduren für den Datenimport/-export
Nach verschiedenen Parametern können Sie die gesamte Datenbank/ein einzelnes Tabellenaufrufbeispiel importieren/exportieren:
Beispiel für einen --export-Aufruf
----Eine einzelne Tabelle exportieren
exec file2table 'zj','','','xzkh_sa..region information','c:zj.txt',1
----Exportieren Sie die gesamte Datenbank
exec file2table 'zj','','','xzkh_sa','c:docman',1
--Beispiel für einen Import-Anruf
----Eine einzelne Tabelle importieren
exec file2table 'zj','','','xzkh_sa..region information','c:zj.txt',0
----Importieren Sie die gesamte Datenbank
exec file2table 'zj','','','xzkh_sa','c:docman',0
*/
falls vorhanden (wählen Sie 1 aus sysobjects aus, wobei name='file2table' und objectproperty(id,'isprocedure')=1)
Drop-Prozedur file2table
gehen
Erstellen Sie die Prozedur file2table
@servername varchar(200) –Servername
,@username varchar(200) --Benutzername, wenn die NT-Überprüfungsmethode verwendet wird, ist er leer''
,@password varchar(200) –Passwort
,@tbname varchar(500) --database.dbo.table name Wenn Sie nicht angeben:.dbo.table name, werden alle Benutzertabellen der Datenbank exportiert.
,@filename varchar(1000) --Import-/Exportpfad/Dateiname Wenn der Parameter @tbname angibt, dass die gesamte Datenbank exportiert werden soll, dann ist dieser Parameter der Dateispeicherpfad und der Dateiname verwendet automatisch den Tabellennamen.txt
,@isout bit --1 ist Export, 0 ist Import
als
deklarieren Sie @sql varchar(8000)
if @tbname like '%.%.%' – Wenn ein Tabellenname angegeben wird, wird eine einzelne Tabelle direkt exportiert
beginnen
set @sql='bcp '+@tbname
+case wenn @isout=1 dann ' out ' else ' in ' end
+' " '+@Dateiname+' " /w'
+' /s '+@servername
+case when isnull(@username,'')='' then '' else ' /u '+@username end
+' /p '+isnull(@password,'')
exec master..xp_cmdshell @sql
Ende
anders
begin --exportieren Sie die gesamte Datenbank, definieren Sie Cursor und entfernen Sie alle Benutzertabellen
deklariere @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)
öffne #tb
Als nächstes von #tb nach @m_tbname abrufen
während @@fetch_status=0
beginnen
set @sql='bcp '+@tbname+'..'+@m_tbname
+case wenn @isout=1 dann ' out ' else ' in ' end
+' " '+@Dateiname+@m_tbname+'.txt " /w'
+' /s '+@servername
+case when isnull(@username,'')='' then '' else ' /u '+@username end
+' /p '+isnull(@password,'')
exec master..xp_cmdshell @sql
Als nächstes von #tb nach @m_tbname abrufen
Ende
schließen #tb
#tb freigeben
Ende
gehen
/****************************excel zu txt************************* ****** *********************/
Möchte nutzen
select * into opendatasource(...) from opendatasource(...)
Implementierung des Imports des Inhalts einer Excel-Datei in eine Textdatei
Angenommen, es gibt in Excel zwei Spalten, die erste Spalte ist der Name und die zweite Spalte ist die Kontonummer (16 Ziffern).
Und die Bankkontonummer wird nach dem Export in eine Textdatei in zwei Teile geteilt, die ersten 8 Ziffern und die letzten 8 Ziffern werden getrennt.
Wenn Sie es mit der obigen Anweisung einfügen möchten, muss die Textdatei vorhanden sein und eine Zeile enthalten: Name, Bankkontonummer 1, Bankkontonummer 2
Anschließend können Sie zum Einfügen die folgende Anweisung verwenden. Beachten Sie, dass der Dateiname und das Verzeichnis entsprechend Ihrer tatsächlichen Situation geändert werden können.
hineinstecken
opendatasource('microsoft.jet.oledb.4.0'
,'text;hdr=yes;database=c:'
)...[aa#txt]
--,aa#txt)
--*/
Name auswählen, Bankkontonummer 1=links (Bankkontonummer, 8), Bankkontonummer 2 = rechts (Bankkontonummer, 8)
aus
opendatasource('microsoft.jet.oledb.4.0'
,'excel 5.0;hdr=yes;imex=2;database=c:a.xls'
--,Blatt1$)
)...[sheet1$]
Wenn Sie Textdateien direkt einfügen und generieren möchten, verwenden Sie bcp
deklarieren Sie @sql varchar(8000),@tbname varchar(50)
--Importieren Sie zunächst den Inhalt der Excel-Tabelle in eine globale temporäre Tabelle
select @tbname='[##temp'+cast(newid() as varchar(40))+']'
,@sql='Name auswählen, Bankkontonummer 1=links (Bankkontonummer, 8), Bankkontonummer 2 = rechts (Bankkontonummer, 8)
in '+@tbname+' von
opendatasource(''microsoft.jet.oledb.4.0''
,''excel 5.0;hdr=yes;imex=2;database=c:a.xls''
)...[sheet1$]'
exec(@sql)
--Dann verwenden Sie bcp, um aus der globalen temporären Tabelle in eine Textdatei zu exportieren
set @sql='bcp " '+@tbname+' " out "c:aa.txt" /s"(local)" /p"" /c'
exec master..xp_cmdshell @sql
--Temporäre Tabelle löschen
exec('drop table '+@tbname )
Gespeicherte Prozedur zum Importieren und Exportieren von Dateien in die Datenbank mithilfe von bcp:
/*--bcp-Import und Export von Binärdateien
Unterstützt den Import/Export von Bild-, Text- und Ntextfeldern
image ist für Binärdateien geeignet, ntext ist für Textdatendateien geeignet
Hinweis: Beim Importieren werden alle Zeilen überschrieben, die die Bedingungen erfüllen
Beim Exportieren werden alle Zeilen, die die Bedingungen erfüllen, auch in die angegebene Datei exportiert.
Diese gespeicherte Prozedur verwendet bcp nur zum Implementieren von Zou Jian 2003.08-----------------*/
/*--Aufrufbeispiel
--Datenexport
exec p_binaryio 'zj','','','acc_demo data..tb','img','c:zj1.dat'
--Datenexport
exec p_binaryio 'zj','','','acc_demo data..tb','img','c:zj1.dat','',0
--*/
falls vorhanden (wählen Sie * aus dbo.sysobjects aus, wobei id = object_id(n'[dbo].[p_binaryio]') und objectproperty(id, n'isprocedure') = 1)
Drop-Prozedur [dbo].[p_binaryio]
gehen
proc p_binaryio erstellen
@servename varchar (30),--Servername
@username varchar (30), --username
@password varchar (30), --password
@tbname varchar (500), --database..Tabellenname
@fdname varchar (30), --Feldname
@fname varchar (1000), --directory + Dateiname, während der Verarbeitung verwenden/überschreiben: @filename+.bak
@tj varchar (1000)='', --processing Bedingungen: Wenn die Bedingung @fdname enthält, geben Sie bitte das Tabellennamenpräfix an
@isout bit=1 --1 Export ((Standard), 0 Import
als
Deklarieren Sie @fname_in varchar(1000) --bcp, der den Namen der Antwortdatei verarbeitet
,@fsize varchar(20) – Die Größe der zu verarbeitenden Datei
,@m_tbname varchar(50) --temporärer Tabellenname
,@sql varchar(8000)
--Ermitteln Sie die Größe der importierten Datei
wenn @isout=1
setze @fsize='0'
anders
beginnen
Tabelle #tb erstellen (optionaler Name varchar (20), Größe int
, Erstellungsdatum varchar(10), Erstellungszeit varchar(20)
, Datum des letzten Schreibvorgangs varchar(10), Uhrzeit des letzten Schreibvorgangs varchar(20)
,Datum des letzten Zugriffs varchar(10), Zeit des letzten Zugriffs varchar(20),Merkmal int)
in #tb einfügen
exec master..xp_getfiledetails @fname
Wählen Sie @fsize=sizefrom #tb
Drop-Tisch #tb
wenn @fsize null ist
beginnen
„Datei nicht gefunden“ drucken
zurückkehren
Ende
Ende
--Antwortdatei für die Datenverarbeitung generieren
set @m_tbname='[##temp'+cast(newid() as varchar(40))+']'
set @sql='select * into '+@m_tbname+' from(
Wählen Sie als Typ null aus
Union alle wählen 0 als Präfix
Union alle wählen „+@fsize+“ als Länge
Union alle wählen Null als Ende aus
Union alle wählen Null als Format
) A'
exec(@sql)
Wählen Sie @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
--Temporäre Tabelle löschen
set @sql='drop table '+@m_tbname
exec(@sql)
wenn @isout=1
beginnen
set @sql='bcp "select top 1 '+@fdname+' from '
+@tbname+case isnull(@tj,'') wenn '' dann ''
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
Ende
anders
beginnen
--Temporäre Tabellen für den Datenimport vorbereiten
set @sql='select top 0 '+@fdname+' into '
+@m_tbname+' von ' +@tbname
exec(@sql)
--Daten in eine temporäre Tabelle importieren
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
--Daten in formale Tabellen importieren
set @sql='update '+@tbname
+' set '+@fdname+'=b.'+@fdname
+' von '+@tbname+' a,'
+@m_tbname+' b'
+case isnull(@tj,'') wenn '' dann ''
else ' where '+@tj end
exec(@sql)
--Löschen Sie die temporäre Datenverarbeitungstabelle
set @sql='drop table '+@m_tbname
Ende
--Antwortdatei zur Datenverarbeitung löschen
set @sql='del '+@fname_in
exec master..xp_cmdshell @sql