時(shí)間:2015/6/28來(lái)源:IT貓撲網(wǎng)作者:網(wǎng)管聯(lián)盟我要評(píng)論(0)
在涉及到sql server編程或是管理時(shí)一定會(huì)用到數(shù)據(jù)的導(dǎo)入與導(dǎo)出, 導(dǎo)入導(dǎo)出的方法有多種,結(jié)合我在做項(xiàng)目時(shí)的經(jīng)歷做一下匯總:
1. SQL Server導(dǎo)入導(dǎo)出向?qū)?這種方式是最方便的.
導(dǎo)入向?qū)?微軟提供了多種數(shù)據(jù)源驅(qū)動(dòng),包括SQL Server Native Cliant, OLE DB For oracle,Flat File Source,access,excel,XML等,基本上可以滿足系統(tǒng)開(kāi)發(fā)的需求.
同樣導(dǎo)出向?qū)б灿型瑯佣嗟哪康脑打?qū)動(dòng),可以把數(shù)據(jù)導(dǎo)入到不同的目的源.
對(duì)數(shù)據(jù)庫(kù)管理人員來(lái)說(shuō)這種方式簡(jiǎn)單容易操作,導(dǎo)入時(shí)SQL Server也會(huì)幫你建立相同結(jié)構(gòu)的Table.
2. 用.NET的代碼實(shí)現(xiàn)(比如有一個(gè)txt或是excel的檔案,到讀取到DB中)
2.1 最為常見(jiàn)的就是循環(huán)讀取txt的內(nèi)容,然后一條一條的塞入到Table中.這里不再贅述.
2.2 集合整體讀取,使用OLEDB驅(qū)動(dòng).
代碼如下:
- string strOLEDBConnect = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\1\;
- Extended Properties='text;HDR=Yes;FMT=Delimited'";
- OleDbConnection conn = new OleDbConnection(strOLEDBConnect);conn.Open();
- SQLstmt = "select * from 1.txt";
- //讀取.txt中的數(shù)據(jù)DataTable dt=new DataTable();
- OleDbDataAdapter da = new OleDbDataAdapter(SQLstmt, conn);da.Fill(dt);
- //在DataSet的指定范圍中添加或刷新行以匹配使用DataSet、DataTable
- 和IDataReader 名稱(chēng)的數(shù)據(jù)源中的行。
- if(dt.Rows.Count>0)
- foreach(DataRow dr in dt.Rows)
- {
- SQLstmt = "insert into MyTable values('" + dr..."
語(yǔ)法:
- bcp {[[database_name.][schema].]{table_name | view_name} | "query"}
- {in | out | queryout | format} data_file
- [-mmax_errors] [-fformat_file] [-x] [-eerr_file]
- [-Ffirst_row] [-Llast_row] [-bbatch_size]
- [-ddatabase_name] [-n] [-c] [-N] [-w] [-V (70 | 80 | 90 )]
- [-q] [-C { ACP | OEM | RAW | code_page } ]
- [-tfield_term]
- [-rrow_term] [-iinput_file] [-ooutput_file] [-apacket_size]
- [-S [server_name[\instance_name]]] [-Ulogin_id] [-Ppassword]
- [-T] [-v] [-R] [-k] [-E] [-h"hint [,...n]"]
如:
如:
4.BULK INSERT. T-SQL的命令,允許直接導(dǎo)入數(shù)據(jù)
語(yǔ)法:
- BULK INSERT
- [ database_name. [ schema_name ] . | schema_name. ] [ table_name | view_name ]
- FROM 'data_file'
- [ WITH ( [ [ , ] BATCHSIZE =batch_size ]
- [ [ , ] CHECK_CONSTRAINTS ]
- [ [ , ] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]
- [ [ , ] DATAFILETYPE =
- { 'char' | 'native'| 'widechar' | 'widenative' } ]
- [ [ , ] FIELDTERMINATOR = 'field_terminator' ]
- [ [ , ] FIRSTROW = first_row ]
- [ [ , ] FIRE_TRIGGERS ]
- [ [ , ] FORMATFILE ='format_file_path' ]
- [ [ , ] KEEPIDENTITY ]
- [ [ , ] KEEPNULLS ]
- [ [ , ] KILOBYTES_PER_BATCH =kilobytes_per_batch ]
- [ [ , ] LASTROW =last_row ]
- [ [ , ] MAXERRORS =max_errors ]
- [ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ]
- [ [ , ] ROWS_PER_BATCH =rows_per_batch ]
- [ [ , ] ROWTERMINATOR ='row_terminator' ]
- [ [ , ] TABLOCK ]
- [ [ , ] ERRORFILE ='file_name' ] &nb
關(guān)鍵詞標(biāo)簽:SQL Server
相關(guān)閱讀
熱門(mén)文章 SqlServer2005對(duì)現(xiàn)有數(shù)據(jù)進(jìn)行分區(qū)具體步驟sql server系統(tǒng)表?yè)p壞的解決方法MS-SQL2005服務(wù)器登錄名、角色、數(shù)據(jù)庫(kù)用戶Access、SQL Server、Oracle常見(jiàn)應(yīng)用的區(qū)別
人氣排行 如何遠(yuǎn)程備份(還原)SQL2000數(shù)據(jù)庫(kù)SQL2000數(shù)據(jù)庫(kù)遠(yuǎn)程導(dǎo)入(導(dǎo)出)數(shù)據(jù)配置和注冊(cè)O(shè)DBC數(shù)據(jù)源-odbc數(shù)據(jù)源配置教程SQL2000和SQL2005數(shù)據(jù)庫(kù)服務(wù)端口查看或修改SQL Server 2005降級(jí)到2000的正確操作步驟修改Sql Server唯一約束教程淺談JSP JDBC來(lái)連接SQL Server 2005的方法SQL Server創(chuàng)建表語(yǔ)句介紹