二、SQL SERVER 和EXCEL的数据导入导出 1、在SQL SERVER里查询Excel数据: -- ====================================================== SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$] 下面是个查询的示例,它通过用于 Jet 的 OLE DB 提供程序查询 Excel 电子表格。 SELECT * FROM OpenDataSource ( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions -------------------------------------------------------------------------------------------------2、将Excel的数据导入SQL server : -- ====================================================== SELECT * into newtable FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$] 实例: SELECT * into newtable FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions -------------------------------------------------------------------------------------------------3、将SQL SERVER中查询到的数据导成一个Excel文件 -- ====================================================== T-SQL代码: EXEC master..xp_cmdshell 'bcp 库名.dbo.表名out c:\Temp.xls -c -q -S"servername" -U"sa" -P""' 参数:S 是SQL服务器名;U是用户;P是密码 说明:还可以导出文本文件等多种格式 实例:EXEC master..xp_cmdshell 'bcp saletesttmp.dbo.CusAccount out c:\temp1.xls -c -q -S"pmserver" -U"sa" -P"sa"' EXEC master..xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout C:\ authors.xls -c -Sservername -Usa -Ppassword' 在VB6中应用ADO导出EXCEL文件代码: Dim cn As New ADODB.Connection cn.open "Driver={SQL Server};Server=WEBSVR;DataBase=WebMis;UID=sa;WD=123;" cn.execute "master..xp_cmdshell 'bcp "SELECT col1, col2 FROM 库名.dbo.表名" queryout E:\DT.xls -c -Sservername -Usa -Ppassword'" ------------------------------------------------------------------------------------------------4、在SQL SERVER里往Excel插入数据: -- ====================================================== insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\Temp.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...table1 (A1,A2,A3) values (1,2,3)T-SQL代码: INSERT INTO OPENDATASOURCE('Microsoft.JET.OLEDB.4.0', 'Extended Properties=Excel 8.0;Data source=C:\training\inventur.xls')...[Filiale1$] (bestand, produkt) VALUES (20, 'Test') -------------------------------------------------------------------------------------------------用OPENROWSET的方法:--如果接受数据导入的表已经存在 insert into 表 select * from OPENROWSET('MICROSOFT.JET.OLEDB.4.0' ,'Excel 5.0;HDR=YES;DATABASE=c:\test.xls',sheet1$)--如果导入数据并生成表 select * into 表 from OPENROWSET('MICROSOFT.JET.OLEDB.4.0' ,'Excel 5.0;HDR=YES;DATABASE=c:\test.xls',sheet1$) /*===================================================================*/ --从SQL数据库中,导出数据到Excel,如果Excel文件已经存在,而且已经按照要接收的数据创建好表头,就可以简单的用: insert into OPENROWSET('MICROSOFT.JET.OLEDB.4.0' ,'Excel 5.0;HDR=YES;DATABASE=c:\test.xls',sheet1$) select * from 表 --如果Excel文件不存在,也可以用BCP来导成类Excel的文件,注意大小写: --导出表的情况 EXEC master..xp_cmdshell 'bcp 数据库名.dbo.表名 out "c:\test.xls" /c /S"服务器名" /U"用户名" -P"密码"'--导出查询的情况 EXEC master..xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout "c:\test.xls" /c /S"服务器名" /U"用户名" -P"密码"' /*--说明: c:\test.xls 为导入/导出的Excel文件名.要求文件在SQL服务器上,或者SQL服务器可以访问的共享目录上 sheet1$ 为Excel文件的工作表名,一般要加上$才能正常使用. --*/本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/htl258/archive/2009/04/05/4049019.aspx
也可以用SQL自带的导入导出工具.
TONY哥 能否 给我份分布式查询的资料?
SQL SERVER 和EXCEL的数据导入导出 1、在SQL SERVER里查询Excel数据: -- ====================================================== SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$] 下面是个查询的示例,它通过用于 Jet 的 OLE DB 提供程序查询 Excel 电子表格。 SELECT * FROM OpenDataSource ( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions -------------------------------------------------------------------------------------------------2、将Excel的数据导入SQL server : -- ====================================================== SELECT * into newtable FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$] 实例: SELECT * into newtable FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions -------------------------------------------------------------------------------------------------3、将SQL SERVER中查询到的数据导成一个Excel文件 -- ====================================================== T-SQL代码: EXEC master..xp_cmdshell 'bcp 库名.dbo.表名out c:\Temp.xls -c -q -S"servername" -U"sa" -P""' 参数:S 是SQL服务器名;U是用户;P是密码 说明:还可以导出文本文件等多种格式 实例:EXEC master..xp_cmdshell 'bcp saletesttmp.dbo.CusAccount out c:\temp1.xls -c -q -S"pmserver" -U"sa" -P"sa"' EXEC master..xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout C:\ authors.xls -c -Sservername -Usa -Ppassword' 在VB6中应用ADO导出EXCEL文件代码: Dim cn As New ADODB.Connection cn.open "Driver={SQL Server};Server=WEBSVR;DataBase=WebMis;UID=sa;WD=123;" cn.execute "master..xp_cmdshell 'bcp "SELECT col1, col2 FROM 库名.dbo.表名" queryout E:\DT.xls -c -Sservername -Usa -Ppassword'" ------------------------------------------------------------------------------------------------4、在SQL SERVER里往Excel插入数据: -- ====================================================== insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\Temp.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...table1 (A1,A2,A3) values (1,2,3)T-SQL代码: INSERT INTO OPENDATASOURCE('Microsoft.JET.OLEDB.4.0', 'Extended Properties=Excel 8.0;Data source=C:\training\inventur.xls')...[Filiale1$] (bestand, produkt) VALUES (20, 'Test')
zhuan /*---------------------------------------------------------------- -- Author :feixianxxx(poofly) -- Date :2010-03-29 14:04:14 -- Version: -- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) Jul 9 2008 14:43:34 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Evaluation Edition on Windows NT 6.1 <X86> (Build 7600: ) -- CONTENT:关于数据大容量的导入导出小结
----------------------------------------------------------------*/ 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.数据导入导出工具的简单用法a.DTS DTS是一组图形工具和可编程对象,是开发者可以将取自完全的不同源的数据析取、转换并合并成一个或者多个。 它的特点就是可以融合完全不同源的数据源 这在企业改进中应用很大 。 这里涉及到一个DTS包,它是一个有组织的链接、DTS任务、DTS转换和工作流约束的集合。 关于DTS的操作请参看相关具体文献。b.BCP 它常用于将大量的数据从另外的程序转移到SQL SERVER表中。当然也可以用于将表中数据传输到数据文件中。 下面是一些BCP的简单用法(关于很多的选项使用看相关文档) ------ SQL code--前序,开启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 ceshi.dbo.test out e:\test.txt -c -Usa -P123456' --如果是WINDOWS身份 直接 exec master..xp_cmdshell 'bcp tempdb.dbo.test out e:\test.txt -T -c' EXEC master..xp_cmdshell 'bcp ceshi.dbo.test out e:\text.txt -t -c'
--2将TEXT.txt文件中的数据复制到test1表 select * into test1 from test where 1=2 exec 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使用工具快。 小例子: SQL code--truncate table test BULK INSERT tempdb..test FROM 'E:\test.txt' WITH ( FIELDTERMINATOR =',',--字段分割符号 ROWTERMINATOR ='\n'--换行符号 ) select * from test /* id value ----------- ----------- 1 s1 2 asds 3 sadsa 100 2asda*/ps:只写最简单用法,具体参数很多,参考MSDNd.分布式查询SQL code--包含访问 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 数据库中. SELECT a.* 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 Provide r for Jet ----访问 Microsoft Access Northwind 数据库中的 Customers 表。 SELECT CustomerID, CompanyName FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb'; 'admin';'',Customers) GO--c.使用 OPENROWSET 将文件数据大容量插入 varchar(max) 列中 /* 为了导入大型对象数据,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 GO select * from my_Test /* Document ------------------------------------------------------- ASDSADASDSADSADSAFKJHFAS HKLASJHASHBKDSAHKJDHSAKJDHSAKDHSAKDHSA */ e.SELECT INTO关于这个的用法 相信大家都很清楚了 我就不说明了。 5。优化导入导出数据的一些方法1。使用最小日志记录: a.恢复模式是简单模式或者大容量日志记录模式。如果你是完整模式,可以在进行操作前改成大容量日志模式,插入后改回来 b.目的表没有触发器,没有索引,指定了TABLOCK2。将数据从多个客户端并行导入到单个表: a.如果是完整恢复模式,改成大容量日志模式 b.指定了TABLOCK c.表上没有索引3。使用批处理:通过设置BCP或者BULK INSERT的相关选项,是用于可以指定在操作过程中发给SQL的每个批处理的行数。4。禁用触发器和约束:默认情况下是禁用的。如果要检查,可以在复制完成后进行一次更新操作(当然值不可以变) 5。对数据文件中的数据排序:通过设置ORDER提示,提高性能。默认数据文件是不排序的。6。控制锁定行为:指定大容量操作过程获得一个大容量更新表级锁,这样可以减少表上锁的争夺。7。回避DEFAULT:通过设置相关选项,回避在复制数据到表中时,对有DEFAULT的列插入默认值,而是改成在列中值为NULL。
1、在SQL SERVER里查询Excel数据:
-- ======================================================
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]
下面是个查询的示例,它通过用于 Jet 的 OLE DB 提供程序查询 Excel 电子表格。
SELECT *
FROM OpenDataSource ( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
-------------------------------------------------------------------------------------------------2、将Excel的数据导入SQL server :
-- ======================================================
SELECT * into newtable
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]
实例:
SELECT * into newtable
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
-------------------------------------------------------------------------------------------------3、将SQL SERVER中查询到的数据导成一个Excel文件
-- ======================================================
T-SQL代码:
EXEC master..xp_cmdshell 'bcp 库名.dbo.表名out c:\Temp.xls -c -q -S"servername" -U"sa" -P""'
参数:S 是SQL服务器名;U是用户;P是密码
说明:还可以导出文本文件等多种格式
实例:EXEC master..xp_cmdshell 'bcp saletesttmp.dbo.CusAccount out c:\temp1.xls -c -q -S"pmserver" -U"sa" -P"sa"'
EXEC master..xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout C:\ authors.xls -c -Sservername -Usa -Ppassword'
在VB6中应用ADO导出EXCEL文件代码:
Dim cn As New ADODB.Connection
cn.open "Driver={SQL Server};Server=WEBSVR;DataBase=WebMis;UID=sa;WD=123;"
cn.execute "master..xp_cmdshell 'bcp "SELECT col1, col2 FROM 库名.dbo.表名" queryout E:\DT.xls -c -Sservername -Usa -Ppassword'"
------------------------------------------------------------------------------------------------4、在SQL SERVER里往Excel插入数据:
-- ======================================================
insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Temp.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...table1 (A1,A2,A3) values (1,2,3)T-SQL代码:
INSERT INTO
OPENDATASOURCE('Microsoft.JET.OLEDB.4.0',
'Extended Properties=Excel 8.0;Data source=C:\training\inventur.xls')...[Filiale1$]
(bestand, produkt) VALUES (20, 'Test')
-------------------------------------------------------------------------------------------------用OPENROWSET的方法:--如果接受数据导入的表已经存在
insert into 表 select * from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;DATABASE=c:\test.xls',sheet1$)--如果导入数据并生成表
select * into 表 from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;DATABASE=c:\test.xls',sheet1$)
/*===================================================================*/
--从SQL数据库中,导出数据到Excel,如果Excel文件已经存在,而且已经按照要接收的数据创建好表头,就可以简单的用:
insert into OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;DATABASE=c:\test.xls',sheet1$)
select * from 表
--如果Excel文件不存在,也可以用BCP来导成类Excel的文件,注意大小写:
--导出表的情况
EXEC master..xp_cmdshell 'bcp 数据库名.dbo.表名 out "c:\test.xls" /c /S"服务器名" /U"用户名" -P"密码"'--导出查询的情况
EXEC master..xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout "c:\test.xls" /c /S"服务器名" /U"用户名" -P"密码"'
/*--说明:
c:\test.xls 为导入/导出的Excel文件名.要求文件在SQL服务器上,或者SQL服务器可以访问的共享目录上
sheet1$ 为Excel文件的工作表名,一般要加上$才能正常使用.
--*/本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/htl258/archive/2009/04/05/4049019.aspx
1、在SQL SERVER里查询Excel数据:
-- ======================================================
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]
下面是个查询的示例,它通过用于 Jet 的 OLE DB 提供程序查询 Excel 电子表格。
SELECT *
FROM OpenDataSource ( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
-------------------------------------------------------------------------------------------------2、将Excel的数据导入SQL server :
-- ======================================================
SELECT * into newtable
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]
实例:
SELECT * into newtable
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
-------------------------------------------------------------------------------------------------3、将SQL SERVER中查询到的数据导成一个Excel文件
-- ======================================================
T-SQL代码:
EXEC master..xp_cmdshell 'bcp 库名.dbo.表名out c:\Temp.xls -c -q -S"servername" -U"sa" -P""'
参数:S 是SQL服务器名;U是用户;P是密码
说明:还可以导出文本文件等多种格式
实例:EXEC master..xp_cmdshell 'bcp saletesttmp.dbo.CusAccount out c:\temp1.xls -c -q -S"pmserver" -U"sa" -P"sa"'
EXEC master..xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout C:\ authors.xls -c -Sservername -Usa -Ppassword'
在VB6中应用ADO导出EXCEL文件代码:
Dim cn As New ADODB.Connection
cn.open "Driver={SQL Server};Server=WEBSVR;DataBase=WebMis;UID=sa;WD=123;"
cn.execute "master..xp_cmdshell 'bcp "SELECT col1, col2 FROM 库名.dbo.表名" queryout E:\DT.xls -c -Sservername -Usa -Ppassword'"
------------------------------------------------------------------------------------------------4、在SQL SERVER里往Excel插入数据:
-- ======================================================
insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Temp.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...table1 (A1,A2,A3) values (1,2,3)T-SQL代码:
INSERT INTO
OPENDATASOURCE('Microsoft.JET.OLEDB.4.0',
'Extended Properties=Excel 8.0;Data source=C:\training\inventur.xls')...[Filiale1$]
(bestand, produkt) VALUES (20, 'Test')
/*----------------------------------------------------------------
-- Author :feixianxxx(poofly)
-- Date :2010-03-29 14:04:14
-- Version:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
Jul 9 2008 14:43:34
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Evaluation Edition on Windows NT 6.1 <X86> (Build 7600: )
-- CONTENT:关于数据大容量的导入导出小结
----------------------------------------------------------------*/
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.数据导入导出工具的简单用法a.DTS
DTS是一组图形工具和可编程对象,是开发者可以将取自完全的不同源的数据析取、转换并合并成一个或者多个。
它的特点就是可以融合完全不同源的数据源 这在企业改进中应用很大 。
这里涉及到一个DTS包,它是一个有组织的链接、DTS任务、DTS转换和工作流约束的集合。
关于DTS的操作请参看相关具体文献。b.BCP
它常用于将大量的数据从另外的程序转移到SQL SERVER表中。当然也可以用于将表中数据传输到数据文件中。
下面是一些BCP的简单用法(关于很多的选项使用看相关文档) ------
SQL code--前序,开启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 ceshi.dbo.test out e:\test.txt -c -Usa -P123456'
--如果是WINDOWS身份 直接 exec master..xp_cmdshell 'bcp tempdb.dbo.test out e:\test.txt -T -c'
EXEC master..xp_cmdshell 'bcp ceshi.dbo.test out e:\text.txt -t -c'
--2将TEXT.txt文件中的数据复制到test1表
select * into test1 from test where 1=2
exec 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使用工具快。
小例子:
SQL code--truncate table test
BULK INSERT tempdb..test
FROM 'E:\test.txt'
WITH
(
FIELDTERMINATOR =',',--字段分割符号
ROWTERMINATOR ='\n'--换行符号
)
select * from test
/*
id value
----------- -----------
1 s1
2 asds
3 sadsa
100 2asda*/ps:只写最简单用法,具体参数很多,参考MSDNd.分布式查询SQL code--包含访问 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 数据库中.
SELECT a.*
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 Provide r for Jet
----访问 Microsoft Access Northwind 数据库中的 Customers 表。
SELECT CustomerID, CompanyName
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb';
'admin';'',Customers)
GO--c.使用 OPENROWSET 将文件数据大容量插入 varchar(max) 列中
/*
为了导入大型对象数据,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
GO
select * from my_Test
/*
Document
-------------------------------------------------------
ASDSADASDSADSADSAFKJHFAS HKLASJHASHBKDSAHKJDHSAKJDHSAKDHSAKDHSA
*/
e.SELECT INTO关于这个的用法 相信大家都很清楚了 我就不说明了。
5。优化导入导出数据的一些方法1。使用最小日志记录:
a.恢复模式是简单模式或者大容量日志记录模式。如果你是完整模式,可以在进行操作前改成大容量日志模式,插入后改回来
b.目的表没有触发器,没有索引,指定了TABLOCK2。将数据从多个客户端并行导入到单个表:
a.如果是完整恢复模式,改成大容量日志模式
b.指定了TABLOCK
c.表上没有索引3。使用批处理:通过设置BCP或者BULK INSERT的相关选项,是用于可以指定在操作过程中发给SQL的每个批处理的行数。4。禁用触发器和约束:默认情况下是禁用的。如果要检查,可以在复制完成后进行一次更新操作(当然值不可以变) 5。对数据文件中的数据排序:通过设置ORDER提示,提高性能。默认数据文件是不排序的。6。控制锁定行为:指定大容量操作过程获得一个大容量更新表级锁,这样可以减少表上锁的争夺。7。回避DEFAULT:通过设置相关选项,回避在复制数据到表中时,对有DEFAULT的列插入默认值,而是改成在列中值为NULL。
都可以