/*
实现数据导入/导出的存储过程
根据不同的参数,可以实现导入/导出整个数据库/单个表
调用示例:
--导出调用示例
----导出单个表
Exec File2table 'Zj','','','Xzkh_sa..地区资料','C:\Zj.Txt',1
----导出整个数据库
Exec File2table 'Zj','','','Xzkh_sa','C:\Docman',1
--导入调用示例
----导入单个表
Exec File2table 'Zj','','','Xzkh_sa..地区资料','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)