频道导航

SQLServer 数据导入导出的几种方法小结

2019-01-04 MsSQL 前端之家

前端之家收集整理的这篇文章主要介绍了SQLServer 数据导入导出的几种方法小结，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。

sql Server导入导出向导,这种方式是最方便的.@H_5020@@H502_0@导入向导,微软提供了多种数据源驱动,包括 sql Server Native Cliant,OLE DB For Oracle,Flat File Source,Access,Excel,XML等,基本上可以满足系统开发的需求. @H_5020@同样导出向导也有同样多的目的源驱动,可以把数据导入到不同的目的源. @H502_0@对数据库管理人员来说这种方式简单容易操作,导入时sql Server也会帮你建立相同结构的Table. @H_502_0@2. 用.NET的代码实现(比如有一个txt或是excel的档案,到读取到DB中) @H_502_0@2.1 最为常见的就是循环读取txt的内容,然后一条一条的塞入到Table中.这里不再赘述. @H_502_0@2.2 集合整体读取,使用OLEDB驱动. 代码如下: @H_502_0@<div class="codetitle"><a style="CURSOR: pointer" data="13785" class="copybut" id="copybut13785" onclick="doCopy('code13785')"> 代码如下:

<div class="codebody" id="code13785"> @H_5020@string strOLEDBConnect = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\1\;Extended Properties='text;HDR=Yes;FMT=Delimited'"; @H5020@OleDbConnection conn = new OleDbConnection(strOLEDBConnect); @H5020@conn.Open(); @H502_0@sqlstmt = "select * from 1.txt";//读取.txt中的数据 @H_5020@DataTable dt=new DataTable(); @H502_0@OleDbDataAdapter da = new OleDbDataAdapter(sqlstmt,conn); @H_502_0@da.Fill(dt);//在DataSet的指定范围中添加或刷新行以匹配使用DataSet、DataTable 和IDataReader 名称的数据源中的行。 @H_5020@if(dt.Rows.Count>0) @H5020@foreach(DataRow dr in dt.Rows) @H5020@{ @H502_0@sqlstmt = "insert into MyTable values('" + dr..." @H_5020@

@H502_0@3.BCP,可以用作大容量的数据导入导出,也可以配合xpcmdshell来使用. @H5020@语法: BCP语法 @H502_0@<div class="codetitle"><a style="CURSOR: pointer" data="5499" class="copybut" id="copybut5499" onclick="doCopy('code5499')"> 代码如下:

<div class="codebody" id="code5499"> @H_502_0@bcp {[[database_name.][schema].]{table_name | viewname} | "query"} @H502_0@{in | out | queryout | format} datafile @H502_0@[-mmax_errors] [-fformat_file] [-x] [-eerrfile] @H502_0@[-Ffirst_row] [-Llast_row] [-bbatchsize] @H502_0@[-ddatabasename] [-n] [-c] [-N] [-w] [-V (70 | 80 | 90 )] @H502_0@[-q] [-C { ACP | OEM | RAW | code_page } ] [-tfieldterm] @H502_0@[-rrow_term] [-iinput_file] [-ooutput_file] [-apacketsize] @H502_0@[-S [server_name[\instance_name]]] [-Uloginid] [-Ppassword] @H5020@[-T] [-v] [-R] [-k] [-E] [-h"hint [,...n]"] @H5020@

@H5020@请注意数据导入导出的方向参数:in,out,queryout 如: @H5020@@H5020@如: @H5020@@H502_0@4.BULK INSERT. T-sql的命令,允许直接导入数据 @H_5020@语法: @H502_0@<div class="codetitle"><a style="CURSOR: pointer" data="83473" class="copybut" id="copybut83473" onclick="doCopy('code83473')"> 代码如下:

<div class="codebody" id="code83473"> @H_5020@BULK INSERT @H502_0@[ database_name. [ schema_name ] . | schema_name. ] [ table_name | viewname ] @H502_0@FROM 'datafile' @H5020@[ WITH @H5020@( @H502_0@[ [,] BATCHSIZE =batchsize ] @H502_0@[ [,] CHECKCONSTRAINTS ] @H502_0@[ [,] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'codepage' } ] @H5020@[ [,] DATAFILETYPE = @H5020@{ 'char' | 'native'| 'widechar' | 'widenative' } ] @H502_0@[ [,] FIELDTERMINATOR = 'fieldterminator' ] @H502_0@[ [,] FIRSTROW = firstrow ] @H502_0@[ [,] FIRETRIGGERS ] @H502_0@[ [,] FORMATFILE ='format_filepath' ] @H5020@[ [,] KEEPIDENTITY ] @H5020@[ [,] KEEPNULLS ] @H502_0@[ [,] KILOBYTES_PER_BATCH =kilobytes_perbatch ] @H502_0@[ [,] LASTROW =lastrow ] @H502_0@[ [,] MAXERRORS =maxerrors ] @H5020@[ [,] ORDER ( { column [ ASC | DESC ] } [,...n ] ) ] @H502_0@[ [,] ROWS_PER_BATCH =rows_perbatch ] @H502_0@[ [,] ROWTERMINATOR ='rowterminator' ] @H5020@[ [,] TABLOCK ] @H502_0@[ [,] ERRORFILE ='filename' ] @H5020@)] @H5020@

@H5020@重要参数: @H5020@FIELDTERMINATOR,字段分隔符 @H5020@FIRSTROW:第一个数据行 @H5020@ROWTERMINATOR:行终结符 @H502_0@如: <div class="codetitle"><a style="CURSOR: pointer" data="22994" class="copybut" id="copybut22994" onclick="doCopy('code22994')"> 代码如下:

<div class="codebody" id="code22994"> @H_5020@BULK INSERT dbo.ImportTest @H5020@FROM 'C:\ImportData.txt' @H5020@WITH ( FIELDTERMINATOR =',',FIRSTROW = 2 ) @H502_0@ 5. OPENROWSET也是T-sql的命令,包含有DB连接的信息和其它导入方法不同的是,OPENROWSET可以作为一个目标表参与INSERT,UPDATE,DELETE操作. @H_5020@语法: @H502_0@<div class="codetitle"><a style="CURSOR: pointer" data="4431" class="copybut" id="copybut4431" onclick="doCopy('code4431')"> 代码如下:<div class="codebody" id="code4431"> @H_5020@OPENROWSET @H502_0@( { 'provider_name',{ 'datasource';'userid';'password' @H502_0@| 'providerstring' } @H5020@,{ [ catalog. ] [ schema. ] object @H5020@| 'query' @H5020@} @H502_0@| BULK 'datafile',@H502_0@{ FORMATFILE ='format_file_path' [ ] @H_502_0@| SINGLE_BLOB | SINGLE_CLOB | SINGLENCLOB } @H502_0@} ) ::= @H_502_0@[,CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'codepage' } ] @H502_0@[,ERRORFILE ='filename' ] @H502_0@[,FIRSTROW = firstrow ] @H502_0@[,LASTROW = lastrow ] @H502_0@[,MAXERRORS = maximumerrors ] @H502_0@[,ROWS_PER_BATCH =rows_perbatch ] @H5020@ @H5020@如: @H502_0@<div class="codetitle"><a style="CURSOR: pointer" data="86678" class="copybut" id="copybut86678" onclick="doCopy('code86678')"> 代码如下:<div class="codebody" id="code86678"> @H_5020@INSERT INTO dbo.ImportTest @H5020@SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',@H5020@'Excel 8.0;Database=C:\ImportData.xls','SELECT * FROM [Sheet1$]' WHERE A1 IS NOT NULL) @H5020@ @H5020@6.OPENDATASOURCE @H5020@语法: @H502_0@OPENDATASOURCE ( provider_name,initstring ) @H5020@如: @H502_0@<div class="codetitle"><a style="CURSOR: pointer" data="13381" class="copybut" id="copybut13381" onclick="doCopy('code13381')"> 代码如下:<div class="codebody" id="code13381"> @H_5020@INSERT INTO dbo.ImportTest @H5020@SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',@H5020@'Data Source=C:\ImportData.xls;Extended Properties=Excel 8.0')...[Sheet1$] @H5020@ @H502_0@7.OPENQUERY.是在linked server的基础上执行的查询.所以执行之前必须先建立好link server.OPENQUERY的结果集可以作为一个table参与DML的操作. @H_5020@语法: @H502_0@OPENQUERY (linkedserver,'query') @H5020@如: @H502_0@<div class="codetitle"><a style="CURSOR: pointer" data="33271" class="copybut" id="copybut33271" onclick="doCopy('code33271')"> 代码如下:<div class="codebody" id="code33271"> @H_502_0@EXEC spaddlinkedserver 'ImportData',@H5020@'Jet 4.0','Microsoft.Jet.OLEDB.4.0',@H5020@'C:\ImportData.xls',@H5020@NULL,@H5020@'Excel 8.0' @H5020@GO @H5020@INSERT INTO dbo.ImportTest @H5020@SELECT * @H5020@FROM OPENQUERY(ImportData,'SELECT * FROM [Sheet1$]') @H502_0@ 以上只是简单总结的一些DB数据导入导出的方法及其一些简单的实例,希望对你实践中会有所帮助. @H_502_0@

导入导入导入导出导出导出数据数据数据