/******* Exporter vers Excel
exec master..xp_cmdshell 'bcp régléb.dbo.shanghu sur c:temp1.xls -c -q -s"gnetdata/gnetdata" -u"sa" -p""'
/*********** Importer Excel
sélectionner *
depuis opendatasource( 'microsoft.jet.oledb.4.0',
'source de données="c:test.xls";identifiant utilisateur=admin;mot de passe=;propriétés étendues=excel 5.0')...xactions
select cast(cast(account number as numeric(10,2)) as nvarchar(255))+' ' alias converti
depuis opendatasource( 'microsoft.jet.oledb.4.0',
'source de données="c:test.xls";identifiant utilisateur=admin;mot de passe=;propriétés étendues=excel 5.0')...xactions
/** Importer un fichier texte
exec master..xp_cmdshell 'bcp dbname..tablename dans c:dt.txt -c -sservername -usa -ppassword'
/** Exporter un fichier texte
exec master..xp_cmdshell 'bcp "nom de base de données..nom de table" sur c:dt.txt -c -sservername -usa -ppassword'
Cette phrase doit être mise entre guillemets
ou
exec master..xp_cmdshell 'bcp "select * from dbname..tablename" queryout c:dt.txt -c -sservername -usa -ppassword'
Exporter vers du texte txt, séparé par des virgules
exec master..xp_cmdshell 'bcp "Nom de la bibliothèque..Nom de la table" sur "d:tt.txt" -c -t ,-u sa -p mot de passe'
nom de la bibliothèque d'insertion en bloc..nom de la table
à partir de 'c:test.txt'
avec (
terminateur de champ = ';',
terminateur de ligne = 'n'
)
--/* fichier dbase iv
sélectionnez * parmi
openrowset('microsoft.jet.oledb.4.0'
,'dbase iv;hdr=no;imex=2;database=c:','select * from [Données client 4.dbf]')
--*/
--/* fichier dbase iii
sélectionnez * parmi
openrowset('microsoft.jet.oledb.4.0'
,'dbase iii;hdr=no;imex=2;database=c:','select * from [Données client 3.dbf]')
--*/
--/* base de données foxpro
sélectionnez * depuis openrowset('msdasql',
'driver=pilote Microsoft Visual Foxpro;sourcetype=dbf;sourcedb=c:',
'sélectionnez * dans [aa.dbf]')
--*/
/******************Importer le fichier dbf******************/
sélectionnez * depuis openrowset('msdasql',
'driver=pilote Microsoft Visual Foxpro ;
sourcedb=e:vfp98data;
type de source = dbf',
'select * from customer which country != "usa" commande par pays')
aller
/************************ Exporter vers dbf **************/
Si vous souhaitez exporter des données vers la table foxpro de la structure générée (c'est-à-dire existante), vous pouvez directement utiliser l'instruction SQL suivante
insérer dans openrowset('msdasql',
'driver=pilote Microsoft Visual Foxpro;sourcetype=dbf;sourcedb=c:',
'sélectionnez * dans [aa.dbf]')
sélectionnez * dans le tableau
illustrer:
sourcedb=c: spécifie le dossier où se trouve la table foxpro
aa.dbf spécifie le nom de fichier de la table foxpro.
/******************Exporter pour accéder*********************/
insérer dans openrowset('microsoft.jet.oledb.4.0',
'x:a.mdb';'admin';'',a table) sélectionnez * dans le nom de la base de données..b table
/******************accès à l'importation************************/
insérer dans la table b selet * à partir de openrowset('microsoft.jet.oledb.4.0',
'x:a.mdb';'admin';'',une table)
********************* Importer un fichier XML
déclarer @idoc int
déclarer @doc varchar(1000)
--exemple de document XML
définir @doc ='
<racine>
<client cid= "c1" name="janine" city="issaquah">
<commande oid="o1" date="20/01/1996" montant="3.5" />
<order oid="o2" date="4/30/1997" montant="13.4">le client était très satisfait
</commande>
</client>
<client cid="c2" name="ursula" city="oelde" >
<order oid="o3" date="7/14/1999" montant="100" note="enveloppez-le bleu
blanc rouge">
<urgence>important</urgence>
client heureux.
</commande>
<commande oid="o4" date="20/01/1996" montant="10000"/>
</client>
</racine>
'
-- créer une représentation interne du document XML.
exec sp_xml_preparedocument @idoc sortie, @doc
-- exécute une instruction select à l'aide du fournisseur d'ensemble de lignes openxml.
sélectionner *
depuis openxml (@idoc, '/root/customer/order', 1)
avec (oid char(5),
montant flottant,
commentaire ntext 'text()')
exec sp_xml_removedocument @idoc
/**********************Importer toute la base de données************************ ***** *******************/
Procédure stockée implémentée à l'aide de bcp
/*
Implémenter des procédures stockées pour l'importation/exportation de données
Selon différents paramètres, vous pouvez importer/exporter l'intégralité de l'exemple d'appel de base de données/table unique :
--export exemple d'appel
----Exporter une seule table
exec file2table 'zj','','','xzkh_sa..informations sur la région','c:zj.txt',1
----Exporter la base de données entière
exec file2table 'zj','','','xzkh_sa','c:docman',1
--Importer un exemple d'appel
----Importer une seule table
exec file2table 'zj','','','xzkh_sa..informations sur la région','c:zj.txt',0
----Importer la base de données entière
exec file2table 'zj','','','xzkh_sa','c:docman',0
*/
s'il existe (sélectionnez 1 parmi sysobjects où name='file2table' et objectproperty(id,'isprocedure')=1)
supprimer la procédure file2table
aller
créer une procédure file2table
@servername varchar(200) --nom du serveur
,@username varchar(200) --Nom d'utilisateur, si vous utilisez la méthode de vérification nt, il sera vide''
,@mot de passe varchar(200) --Mot de passe
,@tbname varchar(500) --database.dbo.table name Si vous ne spécifiez pas : .dbo.table name, toutes les tables utilisateur de la base de données seront exportées.
,@filename varchar(1000) --Chemin d'importation/exportation/nom de fichier Si le paramètre @tbname indique d'exporter l'intégralité de la base de données, alors ce paramètre est le chemin de stockage du fichier et le nom du fichier utilise automatiquement le nom de la table.txt.
,@isout bit --1 est l'exportation, 0 est l'importation
comme
déclarer @sql varchar(8000)
if @tbname like '%.%.%' --Si un nom de table est spécifié, une seule table sera exportée directement
commencer
set @sql='bcp '+@tbname
+cas quand @isout=1 puis 'out' else 'in' end
+' " '+@nomfichier+' " /w'
+' /s '+@nom du serveur
+case when isnull (@username,'')='' then '' else ' /u '+@username end
+' /p '+est nul (@mot de passe,'')
maître d'exécution..xp_cmdshell @sql
fin
autre
commencer --exporter l'intégralité de la base de données, définir les curseurs et supprimer toutes les tables utilisateur
déclarer @m_tbname varchar(250)
si c'est vrai (@filename,1)<>'' set @filename=@filename+''
définir @m_tbname='declare #tb curseur pour sélectionner le nom à partir de '+@tbname+'..sysobjects où xtype=''u'''
exécutif (@m_tbname)
ouvrir #tb
récupérer ensuite de #tb dans @m_tbname
tandis que @@fetch_status=0
commencer
set @sql='bcp '+@tbname+'..'+@m_tbname
+cas quand @isout=1 puis 'out' else 'in' end
+' " '+@nomfichier+@nom_m_tb+'.txt " /w'
+' /s '+@nom du serveur
+case when isnull (@username,'')='' then '' else ' /u '+@username end
+' /p '+est nul (@mot de passe,'')
maître d'exécution..xp_cmdshell @sql
récupérer ensuite de #tb dans @m_tbname
fin
fermer #tb
désallouer #tb
fin
aller
/************************Excel vers txt************************ ****** *******************/
Voulez-vous utiliser
sélectionnez * dans opendatasource(...) depuis opendatasource(...)
Implémentation de l'import du contenu d'un fichier Excel dans un fichier texte
Supposons qu'il y ait deux colonnes dans Excel, la première colonne est le nom et la deuxième colonne est le numéro de compte (16 chiffres)
Et le numéro de compte bancaire est divisé en deux parties après exportation vers un fichier texte, les 8 premiers chiffres et les 8 derniers chiffres sont séparés.
Si vous souhaitez l'insérer à l'aide de la déclaration ci-dessus, le fichier texte doit exister et comporter une seule ligne : nom, numéro de compte bancaire 1, numéro de compte bancaire 2
Ensuite, vous pouvez utiliser l'instruction suivante pour insérer. Notez que le nom du fichier et le répertoire peuvent être modifiés en fonction de votre situation réelle.
insérer dans
opendatasource('microsoft.jet.oledb.4.0'
,'texte;hdr=oui;base de données=c:'
)...[aa#txt]
--,aa#txt)
--*/
sélectionnez le nom, numéro de compte bancaire 1 = gauche (numéro de compte bancaire, 8), numéro de compte bancaire 2 = droite (numéro de compte bancaire, 8)
depuis
opendatasource('microsoft.jet.oledb.4.0'
,'excel 5.0;hdr=oui;imex=2;base de données=c:a.xls'
--,feuille1$)
)...[feuille1$]
Si vous souhaitez insérer et générer directement des fichiers texte, utilisez bcp
déclarer @sql varchar(8000),@tbname varchar(50)
--Importez d'abord le contenu du tableau Excel dans un tableau temporaire global
sélectionnez @tbname='[##temp'+cast(newid() as varchar(40))+']'
,@sql='select name, numéro de compte bancaire 1=gauche (numéro de compte bancaire, 8), numéro de compte bancaire 2 = droite (numéro de compte bancaire, 8)
dans '+@tbname+' depuis
opendatasource(''microsoft.jet.oledb.4.0''
,''excel 5.0;hdr=oui;imex=2;base de données=c:a.xls''
)...[feuille1$]'
exécutable (@sql)
--Ensuite, utilisez bcp pour exporter de la table temporaire globale vers un fichier texte
set @sql='bcp " '+@tbname+' " sur "c:aa.txt" /s"(local)" /p"" /c'
maître d'exécution..xp_cmdshell @sql
--Supprimer la table temporaire
exec('drop table '+@tbname )
Procédure stockée d'import et d'export de fichiers vers la base de données à l'aide de bcp :
/*--bcp-Import et export de fichiers binaires
Prend en charge l'importation/exportation de champs image, texte et ntext
image convient aux fichiers binaires texte, ntext convient aux fichiers de données texte
Remarque : Lors de l'importation, toutes les lignes qui remplissent les conditions seront écrasées
Lors de l'exportation, toutes les lignes qui remplissent les conditions seront également exportées vers le fichier spécifié.
Cette procédure stockée utilise uniquement bcp pour implémenter Zou Jian 2003.08-----------------*/
/*--Exemple d'appel
--Exportation de données
exec p_binaryio 'zj','','','acc_demo data..tb','img','c:zj1.dat'
--Exportation de données
exec p_binaryio 'zj','','','acc_demo data..tb','img','c:zj1.dat','',0
--*/
s'il existe (sélectionnez * dans dbo.sysobjects où id = object_id(n'[dbo].[p_binaryio]') et objectproperty(id, n'isprocedure') = 1)
supprimer la procédure [dbo].[p_binaryio]
aller
créer un proc p_binaryio
@servename varchar (30),--nom du serveur
@nom d'utilisateur varchar (30), --nom d'utilisateur
@password varchar (30), --password
@tbname varchar (500), --database..nom de la table
@fdname varchar (30), --Nom du champ
@fname varchar (1000), --directory + nom de fichier, utiliser/écraser pendant le traitement : @filename+.bak
@tj varchar (1000)='', --processing conditions Pour l'importation de données, si la condition contient @fdname, veuillez spécifier le préfixe du nom de la table.
@isout bit=1 --1 export ((par défaut), 0 import
comme
déclarer @fname_in varchar(1000) --bcp traitant le nom du fichier de réponses
,@fsize varchar(20) --La taille du fichier à traiter
,@m_tbname varchar(50) --nom de table temporaire
,@sqlvarchar(8000)
--Obtenir la taille du fichier importé
si @isout=1
définir @fsize='0'
autre
commencer
créer la table #tb (nom facultatif varchar (20), taille int
, date de création varchar(10), heure de création varchar(20)
, date de la dernière opération d'écriture varchar(10), heure de la dernière opération d'écriture varchar(20)
, date du dernier accès varchar (10), heure du dernier accès varchar (20), caractéristique int)
insérer dans #tb
maître d'exécution..xp_getfiledetails @fname
sélectionnez @fsize=sizefrom #tb
supprimer la table #tb
si @fsize est nul
commencer
imprimer 'Fichier introuvable'
retour
fin
fin
--Générer un fichier de réponses pour le traitement des données
définir @m_tbname='[##temp'+cast(newid() as varchar(40))+']'
définissez @sql='select * dans '+@m_tbname+' à partir de (
sélectionnez null comme type
l'union sélectionne tous 0 comme préfixe
l'union sélectionne tous '+@fsize+' comme longueur
l'union sélectionne tous null comme fin
l'union sélectionne tous null comme format
) un'
exécutable (@sql)
sélectionnez @fname_in=@fname+'_temp'
,@sql='bcp " '+@m_tbname+' " out " '+@fname_in
+'" /s" '+@nomserveur
+cas quand isnull (@username,'')='' puis ''
else '" /u" '+@nom d'utilisateur fin
+'" /p"'+est nul (@mot de passe,'')+'" /c'
maître d'exécution..xp_cmdshell @sql
--Supprimer la table temporaire
set @sql='drop table '+@m_tbname
exécutable (@sql)
si @isout=1
commencer
set @sql='bcp "sélectionnez le top 1 '+@fdname+' à partir de '
+@tbname+case isnull (@tj,'') quand '' alors ''
else ' où '+@tj se termine
+'" interrogation " '+@fname
+'" /s" '+@nomserveur
+cas quand isnull (@username,'')='' puis ''
else '" /u" '+@nom d'utilisateur fin
+'" /p"'+est nul (@mot de passe,'')
+'" /i" '+@fname_in+'"'
maître d'exécution..xp_cmdshell @sql
fin
autre
commencer
--Préparer des tables temporaires pour l'importation de données
définir @sql='select top 0 '+@fdname+' dans '
+@m_tbname+' de ' +@tbname
exécutable (@sql)
--Importer des données dans une table temporaire
définir @sql='bcp " '+@m_tbname+' " dans " '+@fname
+'" /s" '+@nomserveur
+cas quand isnull (@username,'')='' puis ''
else '" /u" '+@nom d'utilisateur fin
+'" /p"'+est nul (@mot de passe,'')
+'" /i" '+@fname_in+'"'
maître d'exécution..xp_cmdshell @sql
--Importer des données dans des tableaux formels
set @sql='update '+@tbname
+' set '+@nomfd+'=b.'+@nomfd
+' de '+@tbname+' a,'
+@m_tbname+' b'
+case isnull (@tj,'') quand '' alors ''
else ' où '+@tj se termine
exécutable (@sql)
--Supprimer la table temporaire de traitement des données
set @sql='drop table '+@m_tbname
fin
--Supprimer le fichier de réponses du traitement des données
set @sql='del '+@fname_in
maître d'exécution..xp_cmdshell @sql