declare @IP1 varchar(100)
declare @Uid1 varchar(100)
declare @Pwd1 varchar(100)
set @IP1 = '127.0.0.1'
set @Uid1 = 'sa'
set @Pwd1 = '123'
exec ('select * from OpenDataSource(''SQLOLEDB'',''Data Source='+@IP1+';User ID='+@Uid1+';Password='+@Pwd1+''').Agency.dbo.Property')
declare @IP1 varchar(100)
declare @Uid1 varchar(100)
declare @Pwd1 varchar(100)
set @IP1 = '127.0.0.1'
set @Uid1 = 'sa'
set @Pwd1 = '123'
declare @SQL varchar(500)
set @SQL=
'select * from OpenDataSource(''SQLOLEDB'',''Data Source='+@IP1+';User ID='+@Uid1+';Password='+@Pwd1+''').Agency.dbo.Property'
select @SQL
exec (@SQL)
同样可以指定access 数据库,但注意引号的不同
最近做表复制的时候遇到的问题。。
第1步 删除表2
第2步 筛选表1将数据插入表2
create proc Proc_TableCopy
@IP1 varchar(50),
@Uid1 varchar(50),
@Pwd1 varchar(50),
@IP2 varchar(50),
@Uid2 varchar(50),
@Pwd2 varchar(50)
as
begin Transaction
/*declare @IP1 varchar(50)
set @IP1 = '127.0.0.1'
declare @Uid1 varchar(50)
set @Uid1='sa'
declare @Pwd1 varchar(50)
set @Pwd1 = '123'
declare @IP2 varchar(50)
declare @Uid2 varchar(50)
declare @Pwd2 varchar(50)
set @IP2 = '127.0.0.1'
set @Uid2='sa'
set @Pwd2 = '123'*/
--1 检索源数据表中需要复制的数据信息(初步是设想可以区分新的要筛选的数据)
/*declare @SQL varchar(8000)
set @SQL = 'select * from OpenDataSource(''SQLOLEDB'',''Data Source='+@IP1+';User ID='+@Uid1+';Password='+@Pwd1+''').Agency.dbo.Property'
select @SQL
if Exists(exec(@SQL))*/
--begin
--2 将新增数据写入临时表中
--select * into #temp from OpenDataSource('SQLOLEDB','Data Source=127.0.0.1;User ID=sa;Password=123').Agency.dbo.Property
--3 将目标表中的数据删除
declare @DelSQL varchar(8000)
set @DelSQL = 'delete from OpenDataSource(''SQLOLEDB'',''Data Source='+@IP2+';User ID='+@Uid2+';Password='+@Pwd2+''').pubs.dbo.Property'
--select @DelSQL
exec(@DelSQL)
--4 将临时表中的数据插入到目标表中
declare @InsertSQL varchar(8000)
set @InsertSQL ='insert into OpenDataSource(''SQLOLEDB'',''Data Source='+@IP2+';User ID='+@Uid2+';Password='+@Pwd2+''').pubs.dbo.Property select * from OpenDataSource(''SQLOLEDB'',''Data Source='+@IP1+';User ID='+@Uid1+';Password='+@Pwd1+''').Agency.dbo.Property'
select @InsertSQL
exec (@InsertSQL)
--5 删除临时表
--drop table #temp
--end
--else
--declare @SQL varchar(8000)
--set @SQL = 'select * from OpenDataSource(''SQLOLEDB'',''Data Source='+@IP1+';User ID='+@Uid1+';Password='+@Pwd1+''').Agency.dbo.Property'
--exec(@SQL)
commit Transaction
return 1
rollback Transaction
return 2