区资料'''',''''c:\zj.txt'''',0
----导入整个数据库
exec file2table ''''zj'''','''''''','''''''',''''xzkh_sa'''',''''C:\docman'''',0
*/
if exists(select 1 from sysobjects where name=''''File2Table'''' and objectproperty(id,''''IsProcedure'''')=1)
drop procedure File2Table
go
create procedure File2Table
@servername varchar(200) --服务器名
,@username varchar(200) --用户名,如果用NT验证方式,则为空''''''''
,@password varchar(200) --密码
,@tbname varchar(500) --数据库.dbo.表名,如果不指定:.dbo.表名,则导出数据库的所有用户表
,@filename varchar(1000) --导入/导出路径/文件名,如果@tbname参数指明是导出整个数据库,则这个参数是文件存放路径,文件名自动用表名.txt
,@isout bit --1为导出,0为导入
as
declare @sql varchar(8000)
if @tbname like ''''%.%.%'''' --如果指定了表名,则直接导出单个表
begin
set @sql=''''bcp ''''+@tbname
+case when @isout=1 then '''' out '''' else '''' in '''' end
+'''' "''''+@filename+''''" /w''''
+'''' /S ''''+@servername
+case when isnull(@username,'''''''')='''''''' then '''''''' else '''' /U ''''+@username end
+'''' /P ''''+isnull(@password,'''''''')
exec master..xp_cmdshell @sql
end
else
begin --导出整个数据库,定义游标,取出所有的用户表
declare @m_tbname varchar(250)
if right(@filename,1)<>''''\'''' set @filename=@filename+''''\''''
set @m_tbname=''''declare #tb cursor for select name from ''''+@tbname+''''..sysobjects where xtype=''''''''U''''''''''''
exec(@m_tbname)
open #tb
fetch next from #tb into @m_tbname
while @@fetch_status=0
begin
set @sql=''''bcp ''''+@tbname+''''..''''+@m_tbname
+case when @isout=1 then '''' out '''' else '''' in '''' end
+'''' "''''+@filename+@m_tbname+''''.txt " /w''''
+'''' /S ''''+@servername
+case when isnull(@username,'''''''')='''''''' then '''''''' else '''' /U ''''+@username end
+'''' /P ''''+isnull(@password,'''''''')
exec master..xp_cmdshell @sql
fetch next from #tb into @m_tbname
end
close #tb
deallocate #tb
end
go
/************* Oracle **************/
EXEC sp_addlinkedserver ''''OracleSvr'''',
''''Oracle 7.3'''',
''''MSDAORA'''',
''''ORCLDB''''
GO
delete from openquery(mailser,''''select * from yulin'''')
select * from openquery(mailser,''''select * from yulin'''')
update openquery(mailser,''''select * from yulin where id=15'''')set disorder=555,catago=888
insert into openquery(mailser,''''select disorder,catago from yulin'''')values(333,777)
补充：
对于用bcp导出,是没有字段名的.
用openrowset导出,需要事先建好表.
用openrowset导入,除ACCESS及EXCEL外,均不支持非本机数据导入
上一页 [1] [2] |
