SQL Server数据导入导出的几种方法
来源:互联网 发布:手机mac修改软件 编辑:程序博客网 时间:2024/04/27 21:51
在涉及到SQL Server编程或是管理时一定会用到数据的导入与导出, 导入导出的方法有多种,结合我在做项目时的经历做一下汇总:
1. SQL Server导入导出向导,这种方式是最方便的.
导入向导,微软提供了多种数据源驱动,包括SQL Server Native Cliant, OLE DB For Oracle,Flat File Source,Access,Excel,XML等,基本上可以满足系统开发的需求.
同样导出向导也有同样多的目的源驱动,可以把数据导入到不同的目的源.
对数据库管理人员来说这种方式简单容易操作,导入时SQL Server也会帮你建立相同结构的Table.
2. 用.NET的代码实现(比如有一个txt或是excel的档案,到读取到DB中)
2.1 最为常见的就是循环读取txt的内容,然后一条一条的塞入到Table中.这里不再赘述.
2.2 集合整体读取,使用OLEDB驱动.
代码如下:
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中的数据DataTable dt=new DataTable();OleDbDataAdapter da = new OleDbDataAdapter(SQLstmt, conn);da.Fill(dt);//在DataSet的指定范围中添加或刷新行以匹配使用DataSet、DataTable 和IDataReader 名称的数据源中的行。 if(dt.Rows.Count>0) foreach(DataRow dr in dt.Rows) { SQLstmt = "insert into MyTable values('" + dr..."
3.BCP,可以用作大容量的数据导入导出,也可以配合来使用.
语法:
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]"]
请注意数据导入导出的方向参数:in,out,queryout
如:
如:
4.BULK INSERT. T-SQL的命令,允许直接导入数据
语法:
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' ] )]
重要参数:
FIELDTERMINATOR,字段分隔符
FIRSTROW:第一个数据行
ROWTERMINATOR:行终结符
如:
BULK INSERT dbo.ImportTest
FROM 'C:/ImportData.txt'
WITH ( FIELDTERMINATOR =',', FIRSTROW = 2 )
5. OPENROWSET也是T-SQL的命令,包含有DB连接的信息和其它导入方法不同的是,OPENROWSET可以作为一个目标表参与INSERT,UPDATE,DELETE操作.
语法:
OPENROWSET ( { 'provider_name', { 'datasource';'user_id';'password' | 'provider_string' } , { [ catalog. ] [ schema. ] object | 'query' } | BULK 'data_file', { FORMATFILE ='format_file_path' [ <bulk_options> ] | SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB }} )<bulk_options> ::= [ , CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ] [ , ERRORFILE ='file_name' ] [ , FIRSTROW = first_row ] [ , LASTROW = last_row ] [ , MAXERRORS = maximum_errors ] [ , ROWS_PER_BATCH =rows_per_batch ]
如:
INSERT INTO dbo.ImportTest
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:/ImportData.xls', 'SELECT * FROM [Sheet1$]') WHERE A1 IS NOT NULL
6.OPENDATASOURCE
语法:
OPENDATASOURCE ( provider_name,init_string )
如:
INSERT INTO dbo.ImportTest
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:/ImportData.xls;Extended Properties=Excel 8.0')...[Sheet1$]
7.OPENQUERY.是在linked server的基础上执行的查询.所以执行之前必须先建立好link server.OPENQUERY的结果集可以作为一个table参与DML的操作.
语法:
OPENQUERY (linked_server ,'query')
如:
EXEC sp_addlinkedserver 'ImportData',
'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0',
'C:/ImportData.xls',
NULL,
'Excel 8.0'
GO
INSERT INTO dbo.ImportTest
SELECT *
FROM OPENQUERY(ImportData, 'SELECT * FROM [Sheet1$]')
以上只是简单总结的一些DB数据导入导出的方法及其一些简单的实例,希望对你实践中会有所帮助.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
附转:
使用 SqlBulkCopy 大量复制文字文件之 C# 程序代码
我们在之前的一篇文章(http://www.cnblogs.com/liminzhang/archive/2006/10/20/534471.html)提到如何使用SqlBulkCopy 对象来大量复制文字文件,当时所使用的程序语言是 Visual Basic 2005,有读者希望提供 Visual C# 2005 的程序代码。在此兹将程序代码列示如下,请自行参考之:
// 汇入命名空间。
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.VisualBasic.FileIO;
namespace VC测试项目
{
public partial class Form4 : Form
{
public Form4()
{
InitializeComponent();
}
private string[] currentRow;
private int myRowCount = 1;
private int myBatchCount = 1;
private long myCopiedRows = 0;
private long countStart;
// 建立「章立民研究室」数据表,此处是当作一个中介数据表来使用。
private DataTable myTable = new DataTable("章立民工作室");
private void btnGoBulkCopy_Click(object sender, EventArgs e)
{
this.btnGoBulkCopy.Enabled = false;
// 建立「员工编号」字段。
DataColumn colEmployeeId =
myTable.Columns.Add("员工编号", Type.GetType("System.Int32"));
// 建立「身份证字号」字段。
myTable.Columns.Add("身份证字号", Type.GetType("System.String"));
myTable.Columns["身份证字号"].MaxLength = 10;
myTable.Columns["身份证字号"].AllowDBNull = false;
// 建立「姓名」字段。
myTable.Columns.Add("姓名", Type.GetType("System.String"));
myTable.Columns["姓名"].MaxLength = 12;
// 建立「性别」字段。
myTable.Columns.Add("性别", Type.GetType("System.String"));
// myTable.Columns["性别"].MaxLength = 1;
// 建立「地址」字段。
myTable.Columns.Add("地址", Type.GetType("System.String"));
myTable.Columns["地址"].MaxLength = 41;
// 建立「邮政编码」字段。
myTable.Columns.Add("邮政编码", Type.GetType("System.String"));
myTable.Columns["邮政编码"].MaxLength = 5;
// 建立「出生日期」字段。
myTable.Columns.Add("出生日期", Type.GetType("System.DateTime"));
// 建立「婚姻状况」字段。
myTable.Columns.Add("婚姻状况", Type.GetType("System.String"));
// 建立「雇用日期」字段。
myTable.Columns.Add("雇用日期", Type.GetType("System.DateTime"));
// 建立「起薪」字段。
myTable.Columns.Add("起薪", Type.GetType("System.Double"));
// 建立「目前薪资」字段。
myTable.Columns.Add("目前薪资", Type.GetType("System.Double"));
// 建立「加薪日期」字段。
myTable.Columns.Add("加薪日期", Type.GetType("System.DateTime"));
// 建立「部门」字段。
myTable.Columns.Add("部门", Type.GetType("System.String"));
myTable.Columns["部门"].MaxLength = 10;
using(TextFieldParser myReader = new TextFieldParser(@"Text\章立民工作室.txt"))
{
// 表示文件内容是字符分隔。
myReader.TextFieldType = FieldType.Delimited;
// 定义文字文件的字符分隔符。
myReader.Delimiters = new string[] {","};
// 循环处理文字文件中所有数据列的所有字段。
while(!myReader.EndOfData)
{
try
{
currentRow = myReader.ReadFields();
// 略过标题列
if(myRowCount > 1)
{
myTable.Rows.Add(currentRow);
}
}
catch(MalformedLineException ex)
{
MessageBox.Show(ex.Message);
return;
}
myRowCount += 1;
this.lblBeingCopyedTextRows.Text = myTable.Rows.Count.ToString();
this.lblBeingCopyedTextRows.Refresh();
if(myTable.Rows.Count == 50000)
{
try
{
GoBulkCopy();
}
catch(Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
// 清空资料表。
myTable.Rows.Clear();
myBatchCount += 1;
}
}
// 复制最后一批不足50000 笔的数据记录。
if(myTable.Rows.Count > 0)
{
GoBulkCopy();
}
}
this.lblBeingCopyedTextRows.Text = myTable.Rows.Count.ToString();
this.lblTextFileRowCount.Text =
"来源文字文件的数据笔数:" + (myRowCount - 2).ToString();
this.btnGoBulkCopy.Enabled = true;
}
private void GoBulkCopy()
{
// 利用SqlConnectionStringBuilder 对象来构建连接字符串。
// 由于本范例是在同一个SQL Server 数据库的不同数据表之间进行大量复
// 制作业,因此连接至来源数据库与连接至目标服务器的连接字符串是相同的。
SqlConnectionStringBuilder sqlconStringBuilder =
new SqlConnectionStringBuilder();
sqlconStringBuilder.DataSource = @"(local)\SQLExpress";
sqlconStringBuilder.InitialCatalog = "北风贸易";
sqlconStringBuilder.IntegratedSecurity = true;
// 建立连结至目标SQL Server 数据库的连接。
using(SqlConnection con_bulkcopy =
new SqlConnection(sqlconStringBuilder.ConnectionString))
{
// 开启连接至目标SQL Server 的连接。
con_bulkcopy.Open();
SqlCommand cmdRowCount = new SqlCommand(
"SELECT COUNT(*) FROM dbo.Bulk_Target_章立民工作室;",
con_bulkcopy);
if(myBatchCount == 1)
{
// 计算出目标数据表在执行大量复制作业前有多少笔数据记录。
countStart =
System.Convert.ToInt32(cmdRowCount.ExecuteScalar());
this.lblRowsCountBeforeBulkCopy.Text =
"目标数据表在大量复制前拥有的数据笔数= " +
countStart.ToString();
this.lblRowsCountBeforeBulkCopy.Refresh();
}
// 建立一个SqlBulkCopy 对象以便执行大量复制作业。
using(SqlBulkCopy bcp = new SqlBulkCopy(con_bulkcopy))
{
// 指定目标数据表的名称。
bcp.DestinationTableName = "dbo.Bulk_Target_章立民工作室";
// 如果来源数据表与目标数据表的各个字段顺序没有完全对应,
// 必须在此设定来源字段与目标字段的对应关系。
// 将来源数据写入目标数据表。
bcp.WriteToServer(myTable);
}
// 最后再计算出大量复制了多少笔数据记录。
long countEnd =
System.Convert.ToInt32(cmdRowCount.ExecuteScalar());
// 计算出累计复制笔数。
myCopiedRows = countEnd - countStart;
// 显示出批次与大量复制累计笔数。
this.DataGridView1.Rows.Add(
new string[] {
Convert.ToString(myBatchCount), Convert.ToString(myCopiedRows)});
this.DataGridView1.Refresh();
}
}
}
}
- SQL Server数据导入导出的几种方法
- SQL Server数据导入导出的几种方法
- SQL SERVER使用OpenRowset,、OpenDataSource函数导入、导出数据到Excel 的几种方法(整理)
- SQL SERVER使用OpenRowset,、OpenDataSource函数导入、导出数据到Excel 的几种方法(整理)
- SQL Server导出导入数据方法[转]
- SQL Server导出导入数据方法
- SQL Server导出导入数据方法
- SQL Server导出导入数据方法
- SQL Server导出导入数据方法
- SQL Server导出导入数据方法
- SQL Server导出导入数据方法
- SQL Server导出导入数据方法
- SQL Server导出导入数据方法
- SQL Server导出导入数据方法
- SQL Server导出导入数据方法
- SQL Server导出导入数据方法
- SQL Server导入、导出、备份数据方法
- SQL Server导出导入数据方法
- 微型的Java Web框架 Spark
- 如何配置mysql出现了问题,我建议使用PHPnow绿色版
- Convirture 被管理端机器KVM解决方案的依赖的软件列表
- 说说高通和低通的相互关系(磨皮锐化技术准探讨)
- android SAX解析XML
- SQL Server数据导入导出的几种方法
- 针对SharePoint代码的优化和检查方案
- Magento模型的重写规则
- 周六羽毛球
- 新手必读“嵌入式系统学习11大步骤”
- 讲述C# List排序用法的小细节
- Word管理论文的参考文献
- 世间本如此
- start_kernel->setup_arch->paging_init-->free_area_init_node 之 1