/******* Excel にエクスポート
exec master..xp_cmdshell 'bcp soldb.dbo.shanghu out c:temp1.xls -c -q -s"gnetdata/gnetdata" -u"sa" -p""'
/*********** Excel をインポート
選択 *
opendatasource( 'microsoft.jet.oldb.4.0',
'データ ソース = "c:test.xls";ユーザー ID = 管理者;パスワード =;拡張プロパティ = Excel 5.0')...xactions
select Cast(cast(account number as numeric(10,2)) as nvarchar(255))+' ' 変換されたエイリアス
opendatasource( 'microsoft.jet.oldb.4.0',
'データ ソース = "c:test.xls";ユーザー ID = 管理者;パスワード =;拡張プロパティ = 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 パスワード'
一括挿入ライブラリ名..テーブル名
「c:test.txt」から
と （
フィールドターミネーター = ';',
行終端文字 = 'n'
)
--/* dbase iv ファイル
*から選択してください
openrowset('microsoft.jet.oldb.4.0'
,'dbase iv;hdr=no;imex=2;database=c:','[顧客データ 4.dbf] から * を選択')
--*/
--/* dbase iii ファイル
*から選択してください
openrowset('microsoft.jet.oldb.4.0'
,'dbase iii;hdr=no;imex=2;database=c:','[顧客データ 3.dbf] から * を選択')
--*/
--/* フォックスプロ データベース
select * from openrowset('msdasql',
'driver=microsoft Visual foxpro driver;sourcetype=dbf;sourcedb=c:',
'[aa.dbf] から * を選択')
--*/
/******************dbf ファイルをインポート******************/
select * from openrowset('msdasql',
'ドライバー=マイクロソフト ビジュアル Foxpro ドライバー;
ソースデータベース=e:vfp98data;
ソースタイプ=dbf'、
'select * from customer where country != "usa" 国別に注文')
行く
/************************ dbf にエクスポート ***************/
生成された構造 (つまり、既存の) Foxpro テーブルにデータをエクスポートする場合は、次の SQL ステートメントを直接使用できます。
openrowset('msdasql',
'driver=microsoft Visual foxpro driver;sourcetype=dbf;sourcedb=c:',
'[aa.dbf] から * を選択')
テーブルから * を選択
例証します:
sourcedb=c: は、foxpro テーブルが配置されているフォルダーを指定します
aa.dbf は、foxpro テーブルのファイル名を指定します。
/******************アクセスするためにエクスポート**********************/
openrowset('microsoft.jet.oldb.4.0',
'x:a.mdb';'admin';'',a テーブル) select * from データベース名..b テーブル
/******************インポートアクセス************************/
insert into b table selet * from openrowset('microsoft.jet.oldb.4.0',
'x:a.mdb';'admin';'',テーブル)
********************** XML ファイルをインポートします
@idoc int を宣言する
@doc varchar(1000) を宣言します
--サンプル XML ドキュメント
@doc = を設定します
<ルート>
<顧客 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">お客様は非常に満足しました
</順序>
</顧客>
<顧客 cid="c2" name="ursula" city="oelde" >
<order oid="o3" date="7/14/1999" amount="100" note="ラップイットブルー
白赤">
<緊急>重要</緊急>
幸せな顧客。
</順序>
<order oid="o4" date="1/20/1996" amount="10000"/>
</顧客>
</root>
'
-- XML ドキュメントの内部表現を作成します。
exec sp_xml_preparedocument @idoc 出力、@doc
-- openxml 行セットプロバイダーを使用して select ステートメントを実行します。
選択 *
openxml から (@idoc, '/root/customer/order', 1)
(oid char(5),
金額フロート、
コメント ntext 'text()')
exec sp_xml_removedocument @idoc
/**********************データベース全体をインポートします*********************** ***** *******************/
bcp を使用して実装されたストアド プロシージャ
/*
データのインポート/エクスポート用のストアド プロシージャを実装する
さまざまなパラメータに従って、データベース全体/単一テーブル呼び出しの例をインポート/エクスポートできます。
--export 呼び出しの例
----単一のテーブルをエクスポートする
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
*/
存在する場合 (name='file2table' および objectproperty(id,'isprocedure')=1 の sysobjects から 1 を選択)
ドロッププロシージャ file2table
行く
プロシージャ file2table を作成する
@servername varchar(200) --サーバー名
,@username varchar(200) -- ユーザー名、NT 検証メソッドを使用する場合は空になります。
,@password varchar(200) --パスワード
,@tbname varchar(500) --database.dbo.table name:.dbo.table name を指定しない場合、データベースのすべてのユーザー テーブルがエクスポートされます。
,@filename varchar(1000) -- インポート/エクスポート パス/ファイル名 @tbname パラメーターがデータベース全体をエクスポートすることを示している場合、このパラメーターはファイル ストレージ パスであり、ファイル名には自動的にテーブル名 .txt が使用されます。
,@isout ビット --1 はエクスポート、0 はインポート
として
@sql varchar(8000) を宣言します
if @tbname like '%.%.%' -- テーブル名が指定されている場合、単一のテーブルが直接エクスポートされます。
始める
set @sql='bcp '+@tbname
+case when @isout=1 then ' out ' else ' in ' end
+' " '+@ファイル名+' " /w'
+' /s '+@サーバー名
+case when isnull(@username,'')='' then '' else ' /u '+@username end
+' /p '+isnull(@パスワード,'')
exec master..xp_cmdshell @sql
終わり
それ以外
begin --データベース全体をエクスポートし、カーソルを定義し、すべてのユーザー テーブルを削除します。
@m_tbname varchar(250) を宣言します
if right(@filename,1)<>'' set @filename=@filename+''
set @m_tbname='declare #tb カーソルを選択して名前を'+@tbname+'..sysobjects where xtype=''u''' から選択します
実行者(@m_tbname)
#tb を開く
次を #tb から @m_tbname にフェッチします
while @@fetch_status=0
始める
set @sql='bcp '+@tbname+'..'+@m_tbname
+case when @isout=1 then ' out ' else ' in ' end
+' " '+@ファイル名+@m_tbname+'.txt " /w'
+' /s '+@サーバー名
+case when isnull(@username,'')='' then '' else ' /u '+@username end
+' /p '+isnull(@パスワード,'')
exec master..xp_cmdshell @sql
次を #tb から @m_tbname にフェッチします
終わり
閉じる #tb
#tb の割り当てを解除する
終わり
行く
/************************excel to txt********************** ****** *******************/
使いたい
select * into opendatasource(...) from opendatasource(...)
Excelファイルの内容をテキストファイルにインポートする実装
Excel に 2 つの列があり、最初の列が名前、2 番目の列が口座番号 (16 桁) であるとします。
また、銀行口座番号はテキスト ファイルにエクスポートすると 2 つの部分に分割され、最初の 8 桁と最後の 8 桁が分離されます。
上記のステートメントを使用して挿入する場合は、テキスト ファイルが存在し、名前、銀行口座番号 1、銀行口座番号 2 の 1 行が含まれている必要があります。
次に、次のステートメントを使用して挿入できます。ファイル名とディレクトリは実際の状況に応じて変更できることに注意してください。
に挿入する
opendatasource('microsoft.jet.oldb.4.0'
,'テキスト;hdr=はい;データベース=c:'
)...[aa#txt]
--,aa#txt)
--*/
名前を選択、銀行口座番号 1=左 (銀行口座番号、8)、銀行口座番号 2 = 右 (銀行口座番号、8)
から
opendatasource('microsoft.jet.oldb.4.0'
,'excel 5.0;hdr=yes;imex=2;database=c:a.xls'
--,シート1$)
)...[シート1$]
テキスト ファイルを直接挿入して生成する場合は、bcp を使用します。
@sql varchar(8000),@tbname varchar(50) を宣言します
-- まず Excel テーブルの内容をグローバル一時テーブルにインポートします。
select @tbname='[##temp'+cast(newid() as varchar(40))+']'
,@sql='名前を選択、銀行口座番号 1=左 (銀行口座番号、8)、銀行口座番号 2 = 右 (銀行口座番号、8)
から「+@tbname+」に
opendatasource(''microsoft.jet.oldb.4.0''
,''excel 5.0;hdr=yes;imex=2;database=c:a.xls''
)...[シート1$]'
実行(@sql)
-- 次に、bcp を使用してグローバル一時テーブルからテキスト ファイルにエクスポートします。
set @sql='bcp " '+@tbname+' " out "c:aa.txt" /s"(local)" /p"" /c'
exec master..xp_cmdshell @sql
--一時テーブルの削除
exec('テーブルを削除'+@tbname )
bcp を使用してデータベースにファイルをインポートおよびエクスポートするためのストアド プロシージャ:
/*--bcp-バイナリ ファイルのインポートとエクスポート
画像、テキスト、ntext フィールドのインポート/エクスポートをサポート
image はバイナリ ファイルに適しており、ntext はテキスト データ ファイルに適しています。
注: インポートすると、条件を満たすすべての行が上書きされます。
エクスポート時には、条件を満たすすべての行も指定したファイルにエクスポートされます。
このストアド プロシージャは、bcp のみを使用して Zou Jian 2003.08-----------------*/ を実装します。
/*--呼び出し例
--データエクスポート
exec p_binaryio 'zj','','','acc_demo data..tb','img','c:zj1.dat'
--データエクスポート
exec p_binaryio 'zj','','','acc_demo data..tb','img','c:zj1.dat','',0
--*/
存在する場合 (select * from dbo.sysobjects where id = object_id(n'[dbo].[p_binaryio]') および objectproperty(id, n'isprocedure') = 1)
ドロップ プロシージャ [dbo].[p_binaryio]
行く
プロシージャ p_binaryio を作成する
@servename varchar (30), -- サーバー名
@username varchar (30)、--username
@password varchar (30)、--password
@tbname varchar (500), --database..テーブル名
@fdname varchar (30), --フィールド名
@fname varchar (1000)、--ディレクトリ + ファイル名、処理中に使用/上書き: @filename+.bak
@tj varchar (1000)='', --処理条件 データインポートの場合、条件に @fdname が含まれる場合は、テーブル名のプレフィックスを指定してください。
@isout bit=1 --1 エクスポート ((デフォルト)、0 インポート
として
@fname_in varchar(1000) --bcp 処理応答ファイル名を宣言します
,@fsize varchar(20) -- 処理されるファイルのサイズ
,@m_tbname varchar(50) -- 一時テーブル名
,@sql varchar(8000)
--インポートされたファイルのサイズを取得します。
@isout=1の場合
@fsize='0' を設定します
それ以外
始める
テーブル #tb を作成します (オプションの名前 varchar (20)、サイズ int
、作成日 varchar(10)、作成時刻 varchar(20)
、最後の書き込み操作の日付 varchar(10)、最後の書き込み操作の時間 varchar(20)
,最終アクセス日 varchar(10), 最終アクセス時刻 varchar(20),characteristic int)
#tb に挿入
exec master..xp_getfiledetails @fname
select @fsize=sizefrom #tb
テーブル #tb をドロップします
@fsize が null の場合
始める
「ファイルが見つかりません」を印刷
戻る
終わり
終わり
-- データ処理応答ファイルを生成します。
set @m_tbname='[##temp'+cast(newid() as varchar(40))+']'
set @sql='select * into '+@m_tbname+' from(
タイプとして null を選択する
すべて結合するとプレフィックスとして 0 が選択されます
すべての結合は長さとして「+@fsize+」を選択します
すべて結合して終了として null を選択
すべて結合してフォーマットとして null を選択
）あ'
実行(@sql)
@fname_in=@fname+'_temp' を選択してください
,@sql='bcp " '+@m_tbname+' " out " '+@fname_in
+'" /s" '+@servename
+case when isnull(@username,'')='' then ''
else '" /u" '+@ユーザー名end
+'" /p"'+isnull(@password,'')+'" /c'
exec master..xp_cmdshell @sql
--一時テーブルの削除
set @sql='テーブルをドロップ'+@m_tbname
実行(@sql)
@isout=1の場合
始める
set @sql='bcp "'から上位 1 '+@fdname+'を選択
+@tbname+case isnull(@tj,'') when '' then ''
else ' where '+@tj end
+'" クエリアウト " '+@fname
+'" /s" '+@servename
+case when isnull(@username,'')='' then ''
else '" /u" '+@ユーザー名end
+'" /p"'+isnull(@パスワード,'')
+'" /i" '+@fname_in+'"'
exec master..xp_cmdshell @sql
終わり
それ以外
始める
-- データインポート用の一時テーブルを準備する
set @sql='上位 0 '+@fdname+' into '
+@m_tbname+' from ' +@tbname
実行(@sql)
-- データを一時テーブルにインポートします。
set @sql='bcp " '+@m_tbname+' " in " '+@fname
+'" /s" '+@サービス名
+case when isnull(@username,'')='' then ''
else '" /u" '+@ユーザー名end
+'" /p"'+isnull(@パスワード,'')
+'" /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
実行(@sql)
-- データ処理一時テーブルの削除
set @sql='テーブルをドロップ'+@m_tbname
終わり
-- データ処理応答ファイルを削除します
set @sql='del '+@fname_in
exec master..xp_cmdshell @sql