bulk insert 文本文件数据导入

el/2024/7/24 2:09:39
1。关于大容量数据导入导出的一些方法

  SQL SERVER提供多种工具用于各种数据源的数据导入导出,这些数据源包括本文文件、ODBC数据源、OLE DB数据源、ASCII文本文件和EXCEL电子表格。

  2.常用工具

  DTS:数据转换服务导入导出向导或者DTS设计器创建DTS包

  使用SQL SERVER复制发布数据

  BCP命令提示实用工具实现SQL SERVER实例和数据文件之间的数据导入导出

  BULK INSERT实现从数据文件导入数据到SQL SERVER实例

  分布式查询实现从一个数据源选择数据插入到SQL SERVER实例

  SELECT INTO 语句插入数据表

  3.导入导出的数据

  1。导入数据的目标表必须存在。导出数据的目标文件如果存在,则将重写上面的内容。如果不存在,则BCP自动创建文件

  2。数据文件中的数据必须是字符格式或是先前由bcp工具生成的格式(本机格式)

  3。必须对相应的表拥有足够的权限

  4。数据导入导出工具的简单用法

  4.数据导入导出工具的简单用法

  a.DTS

  DTS是一组图形工具和可编程对象,是开发者可以将取自完全的不同源的数据析取、转换并合并成一个或者多个。

  它的特点就是可以融合完全不同源的数据源 这在企业改进中应用很大 。

  这里涉及到一个DTS包,它是一个有组织的链接、DTS任务、DTS转换和工作流约束的集合。

  关于DTS的操作请参看相关具体文献。

  b.BCP

  它常用于将大量的数据从另外的程序转移到SQL SERVER表中。当然也可以用于将表中数据传输到数据文件中。

  下面是一些BCP的简单用法(关于很多的选项使用看相关文档)

  --前序,开启xp_cmdshell

  --关于xp_cmdshell的一些知识请看http://blog.csdn.net/feixianxxx/archive/2009/08/14/4445603.aspx

  EXEC sp_configure 'show advanced options', 1;RECONFIGURE;

  EXEC sp_configure 'xp_cmdshell', 1;RECONFIGURE;

  --环境

  create table test

  (

  id int,

  value varchar(100)

  )

  go

  insert test values(1,'s1')

  insert test values(2,'s2')

  insert test values(3,'s3')

  insert test values(4,'s4')

  go

  --1将表的数据导出到TEXT.txt文件中

  exec master..xp_cmdshell 'bcp tempdb.dbo.test out e:test.txt -c -Usa -P123456'

  --如果是WINDOWS身份直接xec master..xp_cmdshell 'bcp tempdb.dbo.test out e:test.txt -T -c'

  --2将TEXT.txt文件中的数据复制到test1表

  select*into test1 from test where 1=2exec master..xp_cmdshell 'bcp tempdb.dbo.test1 in e:test.txt -c -Usa -P123456'

  select*from test1

  --3将TEST表的ID字段复制到TEXT.txt中

  exec master..xp_cmdshell 'bcp "SELECT id FROM tempdb.dbo.test" queryout e:test.dat -T -c'

  --4将test表中的第一行移动到text.txt中

  exec master..xp_cmdshell 'bcp "SELECT top 1 * from tempdb.dbo.test " queryout e:test.txt -c -Usa -P123456'

  --关闭xp_cmdshell

  EXEC sp_configure 'show advanced options', 1;RECONFIGURE;

  EXEC sp_configure 'xp_cmdshell', 0;RECONFIGURE;


  c.BULK INSERT

  它只能用于数据导入到SQL SERVER实例中,但是我们一般会选择使用它,因为它比BCP使用工具快。

  小例子:

  --truncate table test

  BULK INSERT tempdb..test

  FROM 'E:test.txt'

  WITH (

  FIELDTERMINATOR =',',--字段分割符号

  ROWTERMINATOR ='n'--换行符号

  )select*from test /*id value----------------------

  1 s12 asds3 sadsa100 2asda*/

  ps:只写最简单用法,具体参数很多,参考MSDN

  d.分布式查询

  --包含访问OLE DB 数据源中的远程数据所需的全部连接信息。

  --当访问链接服务器中的表时,这种方法是一种替代方法,并且是一种使用OLE DB 连接并访问远程数据的一次性的临时方法。

  --对于较频繁引用OLE DB 数据源的情况,请改为使用链接服务器。

  --A.将OPENROWSET 与SELECT 和SQL Server Native Client OLE DB 访问接口一起使用(MSDN)

  以下示例使用 SQL Server Native Client OLE DB 访问接口访问 TEST.A 表,该表位于远程服务器SERVER1 上的 POOFLY 数据库中.

  SELECTa.*FROM OPENROWSET('SQLNCLI', 'Server=SERVER1;Trusted_Connection=yes;',

  'SELECT GroupName, Name, DepartmentID

  FROM POOFLY.TEST.A

  ORDER BY GroupName, Name') AS a;

  --B. 使用Microsoft OLE DB Provider for Jet(MSDN)

  以下示例通过 Microsoft OLE DB Provider for Jet 访问 Microsoft Access Northwind 数据库中的 Customers 表。

  SELECTCustomerID, CompanyName

  FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

  'C:Program FilesMicrosoft OfficeOFFICE11SAMPLESNorthwind.mdb';

  'admin';'',Customers)

  GO

  为了导入大型对象数据,OPENROWSET BULK 子句支持三个选项,允许用户以单行或单列行集导入数据文件的内容。

  你可以指定其中一个大型对象选项,而不是使用格式化文件。

  大型对象选项包括:

  SINGLE_BLOB

  以单行读取data_file 的内容,以varbinary(max) 类型的单列行集返回内容。

  SINGLE_CLOB

  以字符读取指定数据文件的内容,以varchar(max) 类型的单行、单列行集返回内容,使用的是当前数据库的排序规则,例如文本或Microsoft Word 文档。

  SINGLE_NCLOB

  以Unicode 读取指定数据文件的内容,以nvarchar(max) 类型的单行、单列行集返回内容,并使用当前数据库的排序规则。

  以下示例创建一个用于演示的小型表,并将名为 Text1.txt 的文件中的文件数据插入 varchar(max) 列中。

  CREATE TABLE my_Test(Document varchar(max))

  GO

  INSERT INTO my_Test select*FROM OPENROWSET(BULK N'E:test.txt', SINGLE_CLOB) AS Document

  GOselect*from my_Test/*Document

  ASDSADASDSADSADSAFKJHFAS HKLASJHASHBKDSAHKJDHSAKJDHSAKDHSAKDHSA

  e.SELECT INTO

  关于这个的用法 相信大家都很清楚了 我就不说明了。

  5。优化导入导出数据的一些方法

  1。使用最小日志记录:

  a.恢复模式是简单模式或者大容量日志记录模式。如果你是完整模式,可以在进行操作前改成大容量日志模式,插入后改回来

  b.目的表没有触发器,没有索引,指定了TABLOCK

  2。将数据从多个客户端并行导入到单个表:

  a.如果是完整恢复模式,改成大容量日志模式

  b.指定了TABLOCK

  c.表上没有索引

  3。使用批处理:通过设置BCP或者BULK INSERT的相关选项,是用于可以指定在操作过程中发给SQL的每个批处理的行数。

  4。禁用触发器和约束:默认情况下是禁用的。如果要检查,可以在复制完成后进行一次更新操作(当然值不可以变)

  5。对数据文件中的数据排序:通过设置ORDER提示,提高性能。默认数据文件是不排序的。

  6。控制锁定行为:指定大容量操作过程获得一个大容量更新表级锁,这样可以减少表上锁的争夺。

  7。回避DEFAULT:通过设置相关选项,回避在复制数据到表中时,对有DEFAULT的列插入默认值,而是改成在列中值为NULL。

http://www.ngui.cc/el/5557571.html

相关文章

java jdbc向数据库插入大量数据

1899942 ,新疆-乌鲁木齐 1899944 ,新疆-哈密 1899946 ,新疆-吐鲁番 1899948 ,新疆-塔城 1899950 ,新疆-克拉玛依 1899952 ,新疆-克拉玛依 1899954 ,新疆-昌吉 1899956 ,新疆-昌吉 1899958 ,新疆-伊犁 1899960 ,新疆-库尔勒 1899962 ,新疆-库尔勒 1899964 ,新疆-喀什…

jxl Excel模板导出demo

public void upload(){ try { // 源excel文件,通常存储模板(无数据) File sourceFile new File("src/export.xls"); // 目标文件,在模板中填充数据并生成 File targetFile new…

设置session的有效期

server.xml<Context path"/livsorder" docBase"/home/httpd/html/livsorder"   defaultSessionTimeOut"3600" isWARExpanded"true"   isWARValidated"false" isInvokerEnabled"true"   isWorkDirP…

Hibernate 存储过程的调用

[url]http://developer.51cto.com/art/200906/129702.htm[/url]

java 监视文件的变化

[url]http://flypig.iteye.com/blog/870423[/url]

得到DateBox的值

$(#dd).datebox(getValue)

jsp自定义标签常用方法属性

1。支持el表达式&#xff1a;import org.apache.taglibs.standard.lang.support.ExpressionEvaluatorManager;private Object value null;this.value ExpressionEvaluatorManager.evaluate("value", value.toString(), Object.class, this, pageContext); 2.用Bean…

form表单回车提交问题 【转】

form表单回车提交问题 我们有时候希望回车键敲在文本框&#xff08;input element&#xff09;里来提交表单&#xff08;form&#xff09;&#xff0c;但有时候又不希望如此。比如搜索行为&#xff0c;希望输入完关键词之后直接按回车键立即提交表单&#xff0c;而有些复杂表单…

js判断未申明的变量

"undefined" typeof 变量名

html字符过长自动换行css

word-break: break-all; word-wrap: break-word; white-space: normal; width: 125px;