create proc p1 (@目标表名 varchar(100), @源表名 varchar(100))
as
declare @sql varchar(1000)
set @sql = 'delete '+@目标表名+' where id not in (select id from '+@源表名+')'
exec (@sql)
set @sql = 'insert '+@目标表名+' select * from '+@源表名+' where id not in (select id from '+@目标表名+')'
exec (@sql)
set @sql = 'update '+@目标表名+' set col1 = '+@源表名+'.col1 and (其他列)... from '+@源表名+' where '+@目标表名+'.id ='+@源表名+'.id'
exec (@sql)
as
declare @sql varchar(1000)
set @sql = 'delete '+@目标表名+' where id not in (select id from '+@源表名+')'
exec (@sql)
set @sql = 'insert '+@目标表名+' select * from '+@源表名+' where id not in (select id from '+@目标表名+')'
exec (@sql)
set @sql = 'update '+@目标表名+' set col1 = '+@源表名+'.col1 and (其他列)... from '+@源表名+' where '+@目标表名+'.id ='+@源表名+'.id'
exec (@sql)
as
declare @sql varchar(1000)
set @sql = 'delete '+@目标表名+' where id not in (select id from '+@源表名+')'
exec (@sql)
set @sql = 'insert '+@目标表名+' select * from '+@源表名+' where id not in (select id from '+@目标表名+')'
exec (@sql)
set @sql = 'update '+@目标表名+' set col1 = '+@源表名+'.col1 and (其他列)... from '+@源表名+' where '+@目标表名+'.id ='+@源表名+'.id'
exec (@sql)
if exists (select 1 from 目标表 where 主键 not in (select 主键 form 源表))
delect 目标表 where 主键 not in (select 主键 form 源表)2,if exists(select 1 form 源表 where 主键 not in (select 主键 form 目的表))
insert into 目的表 (col1,col2....) select col1,col2.... from 源表 where 主键 not in (select 主键 form 目的表)
然后在组合成删除,插入和更新语句有没有谁能写一个完整的存储过程呀?
WHERE name = 'procSync' AND type = 'P')CREATE PROCEDURE procSync
@stable varchar(40),
@dtable varchar(40)
AS DECLARE @field varchar(8000)
DECLARE @condition varchar(2000)
SET @field=''
SET @condition=''SELECT
a.name,
(case when (SELECT count(*)
FROM sysobjects
WHERE (name in
(SELECT name
FROM sysindexes
WHERE (id = a.id) AND (indid in
(SELECT indid
FROM sysindexkeys
WHERE (id = a.id) AND (colid in
(SELECT colid
FROM syscolumns
WHERE (id = a.id) AND (name = a.name))))))) AND
(xtype = 'PK'))>0 then '1' else '0' end) pk
INTO #tempFROM syscolumns a
inner join sysobjects d
on a.id=d.id and d.name=@dtableselect @condition=@condition+@stable+'.'+name+'='+@dtable+'.'+name+'and' from #temp where pk=1
set @condition=left(@condition,len(@condition-3))
select @field=@field+@stable+'.'+name+'='+@dtable+'.'+name+',' from #temp where pk<>1
set @field=left(@field,len(@field-1))begin transactiondelete from @dtable
where not exists(select 1 from @stable where @condition)insert into @dtable select * from @stable
where not exists(select 1 from @dtable where @condition)update @dtable set @field from @stable where @conditionDROP TABLE #temp
GO--exec procSync 'TSM.dbo.TSM010','SMIS.dbo.VslParticular'
WHERE name = 'procSync' AND type = 'P')CREATE PROCEDURE procSync
@stable varchar(40),
@dtable varchar(40)
AS
DECLARE @SQL NVARCHAR(4000)
DECLARE @field varchar(8000)
DECLARE @condition varchar(2000)
SET @field=''
SET @condition=''SELECT
a.name,
(case when (SELECT count(*)
FROM sysobjects
WHERE (name in
(SELECT name
FROM sysindexes
WHERE (id = a.id) AND (indid in
(SELECT indid
FROM sysindexkeys
WHERE (id = a.id) AND (colid in
(SELECT colid
FROM syscolumns
WHERE (id = a.id) AND (name = a.name))))))) AND
(xtype = 'PK'))>0 then '1' else '0' end) pk
INTO #tempFROM syscolumns a
inner join sysobjects d
on a.id=d.id and d.name=@dtableselect @condition=@condition+@stable+'.'+name+'='+@dtable+'.'+name+'and' from #temp where pk=1
set @condition=left(@condition,len(@condition-3))
select @field=@field+@stable+'.'+name+'='+@dtable+'.'+name+',' from #temp where pk<>1
set @field=left(@field,len(@field-1))begin transaction
SET @SQL='delete from'+ @dtable +
'where not exists(select 1 from @stable where'+ @condition+')'
EXEC SP_EXECUTESQL @SQL
SET @SQL='insert into'+ @dtable+'select * from'+ @stable+
'where not exists(select 1 from'+ @dtable+' where'+ @condition+')'
EXEC SP_EXECUTESQL @SQL
SET @SQL='update'+ @dtable +'set'+ @field +'from'+ @stable+' where'+ @condition
EXEC SP_EXECUTESQL @SQL
DROP TABLE #temp
GO
as
declare @sql varchar(1000)
set @sql = 'delete '+@目标表名+' where id not in (select id from '+@源表名+')'
exec (@sql)
set @sql = 'insert '+@目标表名+' select * from '+@源表名+' where id not in (select id from '+@目标表名+')'
exec (@sql)
set @sql = 'update '+@目标表名+' set col1 = '+@源表名+'.col1 and (其他列)... from '+@源表名+' where '+@目标表名+'.id ='+@源表名+'.id'
exec (@sql)