sql server 数据导入导出

jackey 总结 2009.07.02

 

--使用bcp命令实现数据导入导出

--2. 如何使用bcp导出数据
--(1) 使用bcp导出整个表或视图。(两种实现方式)
EXEC master..xp_cmdshell  'bcp "CACDB_S1000.dbo.tOperator"  out "d:\currency1.txt"  -c  -U"sa" -P"xxxxxx"'--使用密码连接
EXEC master..xp_cmdshell  'bcp CACDB_S1000.dbo.tOperator  out d:\currency1.txt  -c  -U"sa" -P"xxxxxx"'--使用密码连接
--在使用密码登录时需要将-U后的用户名和-P后的密码加上双引号。
--或
EXEC master..xp_cmdshell 'bcp "CACDB_S1000.dbo.tOperator" out "d:\currency2.txt"   -c  -T' --使用信任连接
EXEC master..xp_cmdshell 'bcp CACDB_S1000.dbo.tOperator out d:\currency3.txt   -c  -T' --使用信任连接
--(2) 对要导出的表进行过滤
--bcp不仅可以接受表名或视图名做为参数，也可以接受SQL做为参数。通过SQL语句可以对要导出的表进行过滤，然后导出过滤后的记录。(通过查询sql过滤)
EXEC master..xp_cmdshell 'bcp "SELECT TOP 5 * FROM CACDB_S1000.dbo.tOperator" queryout c:\currency4.txt  -c  -U"sa" -P"xxxxxx"'
--bcp还可以通过简单地设置选项对导出的行进行限制 （通过设置第一行，最后一行参数过滤）
EXEC master..xp_cmdshell 'bcp "SELECT TOP 20 * FROM CACDB_S1000.dbo.tOperator" queryout d:\currency5.txt  -F 10  -L 13  -c  -U"sa" -P"xxxxxx"'
--这条命令使用了两个参数-F 10和-L 13，表示从SELECT TOP 20 * FROM CACDB_S1000.dbo.tOperator所查出来的结果中取第10条到13条记录进行导出。

--3. 如何使用bcp导出格式文件
--(1)
EXEC master..xp_cmdshell 'bcp CACDB_S1000.dbo.tOperator format nul -f d:\currency_format1.fmt  -c -T'
--上述命令将currency表的结构生成了一个格式文件currency_format1.fmt
--这个格式文件记录了这个表的字段（共3个字段）类型、长度、字符和行分割符和字段名等信息。
--(2) bcp导出xml格式的文件
EXEC master..xp_cmdshell 'bcp "select * from CACDB_S1000.dbo.tPlanManager for xml auto, elements " queryout d:\a1.xml -c  -Usa -Pxxxxxx'

---4. 如何使用bcp导入数据
--复制相同的表结构
SELECT TOP 0 * INTO CACDB_S1000.dbo.tOperator1 FROM CACDB_S1000.dbo.tOperator
--将数据导入到表中
EXEC master..xp_cmdshell 'bcp CACDB_S1000.dbo.tOperator1 in d:\currency1.txt   -c  -T'
--导入数据也同样可以使用-F和-L选项来选择导入数据的记录行
EXEC master..xp_cmdshell 'bcp CACDB_S1000.dbo.tOperator1 in d:\currency1.txt   -c -F 10  -L 13 -T'

--使用普通的格式文件
EXEC master..xp_cmdshell 'bcp CACDB_S1000.dbo.tOperator1 in d:\currency1.txt   -F 10  -L 13 -c  -f d:\currency_format1.fmt -T'
--使用xml格式的格式文件
EXEC master..xp_cmdshell 'bcp CACDB_S1000.dbo.tOperator1 in d:\currency1.txt   -F 10  -L 13 -c  -x -f d:\currency_format2.fmt -T'

 

--使用Transact-SQL进行数据导入导出
--(1).使用SELECT INTO导出数据
--注： 在使用SELECT INTO语句时，INTO后跟的表必须在数据库不存在，否则出错.

--复制表结构同时将旧表中的数据导入新表中
SELECT * INTO tOperator2 FROM tOperator
--把数据复制到另外的数据库中
SELECT * INTO CACDB_Log.dbo.tOperator2 FROM tOperator
--(2).使用INSERT INTO 和 UPDATE插入和更新数据
INSERT INTO table1 SELECT * FROM table2
--或
INSERT INTO db2.dbo.table1 SELECT * FROM table2

UPDATE table1 SET table1.f1=table2.f1, table1.f2=table2.f2 FROM table2
WHERE table1.f1=table2.f1

--2. 使用OPENDATASOURCE和OPENROWSET实现在SQL Server数据库和SQL Server数据库之间的数据导入导出
--(1).SQL Server数据库和SQL Server数据库之间的数据导入导出。

--导入数据
SELECT  * INTO tOperator3
    FROM  OPENDATASOURCE(
         'SQLOLEDB',
         'Data Source=127.0.0.1;User ID=sa;Password=xxxxxx'
         ).CACDB_S1000.dbo.tOperator

--导出数据
 INSERT INTO OPENDATASOURCE(
         'SQLOLEDB',
         'Data Source=127.0.0.1;User ID=sa;Password=xxxxxx'
         ).CACDB_Log.dbo.tOperator2 select * from CACDB_S1000.dbo.tOperator 

--在这条语句中OPENDATASOURCE(...)可以理解为SQL Server的一个服务，.CACDB_S1000.dbo.tOperator是这个服务管理的一个数据库的一个表tOperator 。使用INSERT INTO时OPENDATASOURCE(...)后跟的表必须存在,并且如果表中的字段为自增字段，则必须先改成非自增字段在插入，否则插入失败。
--导出数据(用OPENROWSET函数实现)
INSERT INTO OPENROWSET('SQLOLEDB','127.0.0.1';'sa';'xxxxxx', 'select * from CACDB_Log.dbo.tOperator2')
  SELECT * FROM CACDB_S1000.dbo.tOperator 

 

SELECT * INTO CACDB_Log.dbo.tOperator2 FROM
   OPENDATASOURCE('MICROSOFT.JET.OLEDB.4.0','Text;DATABASE=d:\')...[data#txt]

INSERT INTO OPENDATASOURCE('MICROSOFT.JET.OLEDB.4.0','Text;DATABASE=d:\')...[data#txt] SELECT * FROM CACDB_Log.dbo.tOperator2

 

 

存储过程－－－－－导出数据到文件

CREATE     PROCEDURE  jackeytest11
  
AS

declare @bcpString varchar(1000)
set @bcpString='BCP  "select * from  CACDB_S1000.dbo.tYHJBXX"  queryout "d:\jackeytest.txt" -c -S "localhost" -U "sa" -P "xxxxxx"'
EXEC master..xp_cmdshell @bcpString
GO