MSSQL插入更新语句生成
declare @srcDBName nvarchar(100),@destDBName nvarchar(100)
declare @srcTableName nvarchar(100),@destTableName nvarchar(100)
declare @strWhere nvarchar(500)
declare @sql nvarchar(max),@sql_col nvarchar(max)
declare @SFGX int
--源数据库
set @srcDBName=N'eismountainDoc_SZ'
--目的数据库
set @destDBName=N'eismountain_SZ'
--源表
set @srcTableName=N'Build'
--目的表
set @destTableName=N''
--源表条件
set @strWhere=N'SLBH='''''
--是否更新
set @SFGX=0
if @destTableName=N''
set @destTableName=@srcTableName
if @SFGX=0
set @sql='select @sql_col=ISNULL(@sql_col+'','','''')+QUOTENAME(dest.name) from '+@srcDBName+'.dbo.syscolumns src inner join '+@destDBName+'.dbo.syscolumns dest on src.name=dest.name where src.id=OBJECT_ID('''+@srcDBName+'.dbo.'+@srcTableName+''') and dest.id=OBJECT_ID('''+@destDBName+'.dbo.'+@destTableName+''') and dest.status<>128'
else
set @sql='select @sql_col=ISNULL(@sql_col+'','','''')+''dest.''+QUOTENAME(dest.name)+''=src.''+QUOTENAME(src.name) from '+@srcDBName+'.dbo.syscolumns src inner join '+@destDBName+'.dbo.syscolumns dest on src.name=dest.name where src.id=OBJECT_ID('''+@srcDBName+'.dbo.'+@srcTableName+''') and dest.id=OBJECT_ID('''+@destDBName+'.dbo.'+@destTableName+''') and dest.status<>128'
exec sp_executesql @sql,N'@sql_col nvarchar(max) output',@sql_col output
if @SFGX=0
SET @sql='insert into '+@destDBName+'.dbo.'+@destTableName+'('+@sql_col+') select '+@sql_col+' from '+@srcDBName+'.dbo.'+@srcTableName+' where'+@strWhere
else
SET @sql='update dest set '+@sql_col+' from '+@srcDBName+'.dbo.'+@srcTableName+' src inner join '+@destDBName+'.dbo.'+@destTableName+' dest'+' on ... where'+@strWhere
print @sql
