① sql批量導出表為TXT文件到D盤
你可以用SQLCMD執行互動式動作，如：
C:\sqlcmd>sqlcmd
1> SELECT name from sys.databases
2> GO
你也可以試著鍵入如下命令，現實伺服器列表
1>:ServerList
SERVERS:
WUYZ
1>
如果想看其他命令的使用，可以鍵入:Help /?
2、執行SQL腳本文件
你可以在SQLCMD命令上加入參數I來執行SQL腳本文件，例如
C:\sqlcmd>sqlcmd -i test.sql
上面的I選項允許你執行一個腳本文件，另外，你也可以指定O選項，把命令的輸出導出到指定文件,當然，我們也可以在代碼中指定輸出結果，如下
C:\sqlcmd>sqlcmd
1> :out output.txt
2> :r test.sql
3、在腳本中使用變數
SQLCMD支持可以在腳本中接收用戶傳入的變數信息，如下面語句
SET NOCOUNT ON
Select $(Cols) from $(tablename)
GO
上面語句請求2個參數，我們可以通過指定相應的參數信息傳給腳本，如下
C:\sqlcmd>sqlcmd -i test.sql -o Output.txt -v cols="name,object_id,create_date" tablename="sys.objects"
上面語句的用途是：執行TEST.SQL腳本文件，並把輸出的信息輸出到OUTPUT.TXT文件中，並分別指定了COLS,TABLENAME的參數值
4、在腳本中設置變數的值
除了通過外部傳入參數的值外，還可以在內部設置參數的值，如下面例子
e:\sqlcmd\backuptemplate.sql
use master
backup database [$(db)] to disk='$(file)'
e:\sqlcmd\backupsingle.sql
:setvar db msdb
:setvar file c:\temp\msdb.bak
:r e:\sqlcmd\backuptemplate.sql
如果你想知道當然定義了哪些變數，可以使用:listvar命令來顯示
主要命令匯總：
:r filename
:ServerList
:List
:Listvar
:Error filename | STDOUT | STDERR
:Out filename | STDOUT | STDERR
:Perftrace filename | STDOUT | STDERR
:Connect server[\instance] [timeout] [user_name[password] ]
:On Error [exit | ignore]
:SetVar variable value
:Help:XML ON | OFF
主要環境變數匯總：
-a SQLCMDPACKETSIZE
-d SQLCMDDBNAME
-H SQLCMDWORKSTATION
-h SQLCMDHEADERS
-l SQLCMDLOGINTIMEOUT
-m SQLCMDERRORLEVEL
-P SQLCMDPASSWORD
-S SQLCMSSERVER
-s SQLCMDCOLSEP
-t SQLCMDSTATTIMEOUT
-U SQLCMDUSER
-w SQLCMDCOLWIDTH
看看 肯定可以的
② 資料庫操作中如何批量執行多個sql文件
Oracle批量執行SQL文件 1：運行cmd進入命令行界面2：進入到存放批量sql的文件夾下面3：輸入命令 sqlplus 資料庫名/密碼@資料庫 按回車鍵4：輸入spool 指定路徑：\a.log 按回車鍵 （此步驟的主要作用就是追蹤批量執行sql的日誌，路徑隨便寫，只要能找到就行，a.log可以隨便起名，但是必須要.log後綴）5：@start.sql 按回車鍵（在批量sql文件夾下寫一個文件，名字叫start.sql,其中在這個文件裡面寫批量sql的文件名）具體格式如下：@@sql的文件名.sql; @@sql的文件名2.sql;.......6:spool off (此時a.log中所有日誌已經寫入完畢)7：exit 退出sqlplus當然只有一個sql文件，但是裡面有多個insert語句，數據量非常大，此時也可以在命令狀態下執行，前4步是一樣的，第五步就是@文件名.sql;
③ 怎樣用SQL語句批量生成測試數據
ini_set('max_execution_time','0');
$pdo = new PDO("mysql:host=localhost;dbname=test","root","123456");
$sql = "insert into user(id,name,age,created_time) values";
for($i=0; $i<100000; $i++){
$str = strrand(32);
$sql .="('".$str."','zhangsan',21,'2015-12-11'),";
}
$sql = substr($sql,0,strlen($sql)-1);
var_mp($sql);
if($pdo -> exec($sql)){
echo "插入成功！";
echo $pdo -> lastinsertid();
}
sql你是沒辦法批量添加進去的額，，可以用程序處理呀。
④ 處理數據批量生成sql插入語句
處理數據批量生成sql插入語句
最近在做一個天氣預報模塊，首先需要將客戶端公網ip轉換成所在城市，然後將所在城市名轉換成對應的城市代碼，在網上找到了城市代碼，但是需要處理一下，看了看，有三百多城市及對應的城市代碼，想存到資料庫。就想著做一個數據處理自動生成sql語句的工具，提高效率。
1 直轄市
2 "北京","上海","天津","重慶"
3 "101010100","101020100","101030100","101040100"
4
5 特別行政區
6 "香港","澳門"
7 "101320101","101330101"
8
9 黑龍江
10 "哈爾濱","齊齊哈爾","牡丹江","大慶","伊春","雙鴨山","鶴崗","雞西","佳木斯","七台河","黑河","綏化","大興安嶺"
11 "101050101","101050201","101050301","101050901","101050801","101051301","101051201","101051101","101050401","101051002","101050601","101050501","101050701"
12
13 吉林
14 "長春","延吉","吉林","白山","白城","四平","松原","遼源","大安","通化"
15 "101060101","101060301","101060201","101060901","101060601","101060401","101060801","101060701","101060603","101060501"
16
17 遼寧
18 "沈陽","大連","葫蘆島","盤錦","本溪","撫順","鐵嶺","遼陽","營口","阜新","朝陽","錦州","丹東","鞍山"
19 "101070101","101070201","101071401","101071301","101070501","101070401","101071101","101071001","101070801","101070901","101071201","101070701","101070601","101070301"
20
21 內蒙古
22 "呼和浩特","呼倫貝爾","錫林浩特","包頭","赤峰","海拉爾","烏海","鄂爾多斯","通遼"
23 "101080101","101081000","101080901","101080201","101080601","101081001","101080301","101080701","101080501"
24
25 河北
26 "石家莊","唐山","張家口","廊坊","邢台","邯鄲","滄州","衡水","承德","保定","秦皇島"
27 "101090101","101090501","101090301","101090601","101090901","101091001","101090701","101090801","101090402","101090201","101091101"
28
29 河南
30 "鄭州","開封","洛陽","平頂山","焦作","鶴壁","新鄉","安陽","濮陽","許昌","漯河","三門峽","南陽","商丘","信陽","周口","駐馬店"
31 "101180101","101180801","101180901","101180501","101181101","101181201","101180301","101180201","101181301","101180401","101181501","101181701","101180701","101181001","101180601","101181401","101181601"
32
33 山東
34 "濟南","青島","淄博","威海","曲阜","臨沂","煙台","棗庄","聊城","濟寧","菏澤","泰安","日照","東營","德州","濱州","萊蕪","濰坊"
35 "101120101","101120201","101120301","101121301","101120710","101120901","101120501","101121401","101121701","101120701","101121001","101120801","101121501","101121201","101120401","101121101","101121601","101120601"
36
37 山西
38 "太原","陽泉","晉城","晉中","臨汾","運城","長治","朔州","忻州","大同","呂梁"
39 "101100101","101100301","101100601","101100401","101100701","101100801","101100501","101100901","101101001","101100201","101101101"
40
41 江蘇
42 "南京","蘇州","崑山","南通","太倉","吳縣","徐州","宜興","鎮江","淮安","常熟","鹽城","泰州","無錫","連雲港","揚州","常州","宿遷"
43 "101190101","101190401","101190404","101190501","101190408","101190406","101190801","101190203","101190301","101190901","101190402","101190701","101191201","101190201","101191001","101190601","101191101","101191301"
44
45 安徽
46 "合肥","巢湖","蚌埠","安慶","六安","滁州","馬鞍山","阜陽","宣城","銅陵","淮北","蕪湖","毫州","宿州","淮南","池州"
47 "101220101","101221601","101220201","101220601","101221501","101221101","101220501","101220801","101221401","101221301","101221201","101220301","101220901","101220701","101220401","101221701"
48
49 陝西
50 "西安","韓城","安康","漢中","寶雞","咸陽","榆林","渭南","商洛","銅川","延安"
51 "101110101","101110510","101110701","101110801","101110901","101110200","101110401","101110501","101110601","101111001","101110300"
52
53 寧夏
54 "銀川","固原","中衛","石嘴山","吳忠"
55 "101170101","101170401","101170501","101170201","101170301"
56
57 甘肅
58 "蘭州","白銀","慶陽","酒泉","天水","武威","張掖","甘南","臨夏","平涼","定西","金昌"
59 "101160101","101161301","101160401","101160801","101160901","101160501","101160701","101050204","101161101","101160301","101160201","101160601"
60
61 青海
62 "西寧","海北","海西","黃南","果洛","玉樹","海東","海南"
63 "101150101","101150801","101150701","101150301","101150501","101150601","101150201","101150401"
64
65 湖北
66 "武漢","宜昌","黃岡","恩施","荊州","神農架","十堰","咸寧","襄陽","孝感","隨州","黃石","荊門","鄂州"
67 "101200101","101200901","101200501","101201001","101200801","101201201","101201101","101200701","101200201","101200401","101201301","101200601","101201401","101200301"
68
69 湖南
70 "長沙","邵陽","常德","郴州","吉首","株洲","婁底","湘潭","益陽","永州","岳陽","衡陽","懷化","韶山","張家界"
71 "101250101","101250901","101250601","101250501","101251501","101250301","101250801","101250201","101250701","101251401","101251001","101250401","101251201","101250202","101251101"
72
73 浙江
74 "杭州","湖州","金華","寧波","麗水","紹興","衢州","嘉興","台州","舟山","溫州"
75 "101210101","101210201","101210901","101210401","101210801","101210501","101211001","101210301","101210601","101211101","101210701"
76
77 江西
78 "南昌","萍鄉","九江","上饒","撫州","吉安","鷹潭","宜春","新余","景德鎮","贛州"
79 "101240101","101240901","101240201","101240301","101240401","101240601","101241101","101240501","101241001","101240801","101240701"
80
81 福建
82 "福州","廈門","龍岩","南平","寧德","莆田","泉州","三明","漳州"
83 "101230101","101230201","101230701","101230901","101230301","101230401","101230501","101230801","101230601"
84
85 貴州
86 "貴陽","安順","赤水","遵義","銅仁","六盤水","畢節","凱里","都勻"
87 "101260101","101260301","101260208","101260201","101260601","101260801","101260701","101260501","101260401"
88
89 四川
90 "成都","瀘州","內江","涼山","阿壩","巴中","廣元","樂山","綿陽","德陽","攀枝花","雅安","宜賓","自貢","甘孜州","達州","資陽","廣安","遂寧","眉山","南充"
91 "101270101","101271001","101271201","101271601","101271901","101270901","101272101","101271401","101270401","101272001","101270201","101271701","101271101","101270301","101271801","101270601","101271301","101270801","101270701","101271501","101270501"
92
93 廣東
94 "廣州","深圳","潮州","韶關","湛江","惠州","清遠","東莞","江門","茂名","肇慶","汕尾","河源","揭陽","梅州","中山","德慶","陽江","雲浮","珠海","汕頭","佛山"
95 "101280101","101280601","101281501","101280201","101281001","101280301","101281301","101281601","101281101","101282001","101280901","101282101","101281201","101281901","101280401","101281701","101280905","101281801","101281401","101280701","101280501","101280800"
96
97 廣西
98 "南寧","桂林","陽朔","柳州","梧州","玉林","桂平","賀州","欽州","貴港","防城港","百色","北海","河池","來賓","崇左"
99 "101300101","101300501","101300510","101300301","101300601","101300901","101300802","101300701","101301101","101300801","101301401","101301001","101301301","101301201","101300401","101300201"
100
101 雲南
102 "昆明","保山","楚雄","德宏","紅河","臨滄","怒江","曲靖","思茅","文山","玉溪","昭通","麗江","大理"
103 "101290101","101290501","101290801","101291501","101290301","101291101","101291201","101290401","101290901","101290601","101290701","101291001","101291401","101290201"
104
105 海南
106 "海口","三亞","儋州","瓊山","通什","文昌"
107 "101310101","101310201","101310205","101310102","101310222","101310212"
108
109 新疆
110 "烏魯木齊","阿勒泰","阿克蘇","昌吉","哈密","和田","喀什","克拉瑪依","石河子","塔城","庫爾勒","吐魯番","伊寧"
111 "101130101","101131401","101130801","101130401","101131201","101131301","101130901","101130201","101130301","101131101","101130601","101130501","101131001"
112
113 西藏
114 "拉薩","阿里","昌都","那曲","日喀則","山南","林芝"
115 "101140101","101140701","101140501","101140601","101140201","101140301","101140401"
116
117 台灣
118 "台北","高雄"
119 "101340102","101340201"
城市代碼
一看上去很亂的，而且對應關系是每個省城市一行，代碼一行，分別用引號引起，用逗號分隔，每行間都沒有符號分隔，省名沒有用引號。首先是想著把省名去掉，因為每個城市名都是不相同的。想著每兩行兩行的去處理，但是也要費不少功夫，還容易出錯。就想個索性一次性的全處理的演算法。
ps：界面很簡單，上面是輸入數據，中間是轉換，下面是輸出數據。
後台主要代碼：
[csharp] view plain
private void button1_Click(object sender, EventArgs e)
{
string data = textBox1.Text.Replace("r", "").Replace("n", "").Replace("t", "").Replace(" ", "").Replace(" ", "").Replace(" ", "");
MatchCollection matchsdata = matches(data, ""[sS]*?"");
string[,] temps = new string[matchsdata.Count / 2, 2];
int count0 = 0;
int count1 = 0;
string input = string.Empty;
foreach (Match m in matchsdata)
{
string tempdata = m.Value.Replace(""", "");
try
{
int tryp = int.Parse(tempdata);
temps[count1, 1] = tempdata;
count1++;
}
catch (Exception ex)
{
temps[count0, 0] = tempdata;
count0++;
}
}
for (int i = 0; i < (matchsdata.Count / 2); i++)
{
input += "insert into tbl_CityCode(c_city,c_code) values( + temps[i, 0] + , + temps[i, 1] + )rn";
}
textBox2.Text = input;
}
public static MatchCollection matches(string str, string exp)
{
return Regex.Matches(str, exp, RegexOptions.IgnoreCase);
}
首先是將輸入的數據處理，去除換行符，空格什麼的。然後你應該是會得到一行數據，然後通過正則表達式匹配出所有帶引號的數據，你會發現需要的數據全部都是用引號引起來的，但是怎樣區分城市名和城市代碼呢，它們是混在一起的。不用擔心，你發現了嗎？城市名是字元串，城市代碼是一串數字，我們只要將匹配出的數據數組遍歷，每一行數據都去轉換成int類型，這樣城市名的行就會報錯，在catch中捕捉，這一行就是城市名，沒錯的就是城市代碼，把數據一次存到一個二維數組，對應的列中就行了。這樣就會獲得了相對應的城市名和城市代碼。生成的sql語句要對應相應的資料庫表。
表結構：
轉換完了將生成的sql語句放到查詢器中執行就ok了。共處理了349個城市。
最後不放心自己的演算法，隨機抽查了幾條數據，沒有錯誤。
<script type="text/javascript"><!-- google_ad_client = "ca-pub-1944176156128447"; /* cnblogs 首頁橫幅 */ google_ad_slot = "5419468456"; google_ad_width = 728; google_ad_height = 90; //--></script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>
⑤ sql server自動生成批量執行SQL腳本的批處理
場景:
DBA那邊給我導出了所有的存儲 函數等等對象的創建腳本 有上千個文件
現在需要將這些對象創建腳本導入到另外一個庫 如何解決呢？
手動一個個執行顯然不太現實
於是手動寫了一個批處理 將所有的文件形成一個 SQL的腳本 最後以@生成的 SQL腳本方式導仔派賀入到目標庫中
OS環境:WINDOWS xp
腳本內容如下:
@echo off if exist list sql del list sql /q :input cls set input=: set /p input= 請輸入要進念派行判斷的路徑 set "input=%input:"=%" :: 上面這句為判斷%input%中是否存在引號 有則剔除 if "%input%"==":" goto input if not exist "%input%" goto input for /f "delims=" %%i in ( dir /b /a d /s "%input%" ) do echo @@%%~fnxi>>list sql if not exist list sql goto no_file start list sql exit :no_file cls echo %cur_dir% 出現錯誤 未成功生成list sql腳本! pause
使用:
另存為 BAT類型文件後 雙擊執行
輸入你腳本的路徑:
如我的E盤CRY文件夾下 有如下類型的文件:
TEST PRC
TEST FNC(有子文件夾 )
TEST VW
執行該批處理後 最後生成的list sql腳本文件內容如下(執行完批處理後會用默認的編輯器自動打開該文件):
@@E:cryTEST PRC
@@E:cry TEST FNC
@@E:cryTEST VW
打開SQLPLUS 以指定用戶登錄資料庫 然後執行: (我的腳本文件羨磨生成在d盤)
@D:list sql
這樣所有的對象就會自動在指定用戶下生成
lishixin/Article/program/SQLServer/201311/22286
⑥ 如何備份SQL存儲過程批量導出資料庫存儲過程
打開SQL Server Management Studio查詢分析器，找到需要批量導出SQL存儲過程資料庫。右鍵菜單-任務-生成腳本。
進入的生成SQL Server腳本向導。點擊下一步。
選中要導出所有SQL存儲過程的資料庫。繼續下一步。
腳本選項，不用做修改，默認即可。
在選擇對象類型中，選擇存儲過程。
這樣可以看到這個資料庫中所有的SQL存儲過程，可以批量選擇SQL存儲過程，也可以全選所有的SQL存儲過程進行導出。
在腳本模式中，腳本保存到文件，選擇需要導出保存到電腦上的文件夾路徑。單個文件即所有SQL存儲過程都放在一個SQL文件中，不是每個SQL存儲過程獨立一個SQL文件。所以可以選擇每個對象一個文件。
設置向導完成，等待導出完成即可。
⑦ 怎樣把資料庫中的表和數據批量生成sql語句
我在用的是一款名叫 navicat 8.0的mysql管理工具，右鍵選擇要導出的表，選擇mp table就可以了，其實後台跑的也就是上面幾位大俠說的命令。