DECLARE @SQL VARCHAR(8000) SET @SQL='' SELECT @SQL=@SQL+',['+[name]+']=T2.['+[name]+']' FROM SYS.SYSCOLUMNS WHERE ID=OBJECT_ID('BRPMaterial')AND [name]<>'主键' SET @SQL='UPDATE T1 SET'+STUFF(@SQL,1,1,'')+'FROM A T1 JOIN B T2 ON T1.[主键]=T2.[主键]' --PRINT @SQL EXEC(@SQL)
DECLARE @SQL VARCHAR(8000) SET @SQL='' SELECT @SQL=@SQL+',['+[name]+']=T2.['+[name]+']' FROM SYS.SYSCOLUMNS WHERE ID=OBJECT_ID('A')AND [name]<>'主键' SET @SQL='UPDATE T1 SET'+STUFF(@SQL,1,1,'')+'FROM A T1 JOIN B T2 ON T1.[主键]=T2.[主键]' --PRINT @SQL EXEC(@SQL) 只要A表的字段都和B表里面相应的字段同名就可以了
DECLARE @sql VARCHAR(MAX) DECLARE @tablename VARCHAR(50)SET @tablename = 'A'SELECT @sql = CHAR(10) + N'Select ' + STUFF(( SELECT ',' + name FROM sys.columns t WHERE object_id = tb.object_id FOR XML PATH('') ), 1, 1, '') + ' from ' + @tablename FROM sys.columns tb WHERE object_id = OBJECT_ID(@tablename) GROUP BY object_id
SET @SQL=''
SELECT @SQL=@SQL+',['+[name]+']=T2.['+[name]+']' FROM SYS.SYSCOLUMNS WHERE ID=OBJECT_ID('BRPMaterial')AND [name]<>'主键'
SET @SQL='UPDATE T1 SET'+STUFF(@SQL,1,1,'')+'FROM A T1 JOIN B T2 ON T1.[主键]=T2.[主键]'
--PRINT @SQL
EXEC(@SQL)
SET @SQL=''
SELECT @SQL=@SQL+',['+[name]+']=T2.['+[name]+']' FROM SYS.SYSCOLUMNS WHERE ID=OBJECT_ID('A')AND [name]<>'主键'
SET @SQL='UPDATE T1 SET'+STUFF(@SQL,1,1,'')+'FROM A T1 JOIN B T2 ON T1.[主键]=T2.[主键]'
--PRINT @SQL
EXEC(@SQL)
只要A表的字段都和B表里面相应的字段同名就可以了
DECLARE @tablename VARCHAR(50)SET @tablename = 'A'SELECT @sql = CHAR(10) + N'Select '
+ STUFF(( SELECT ',' + name
FROM sys.columns t
WHERE object_id = tb.object_id
FOR
XML PATH('')
), 1, 1, '') + ' from ' + @tablename
FROM sys.columns tb
WHERE object_id = OBJECT_ID(@tablename)
GROUP BY object_id
PRINT @sql 给你个语句能方便一点儿。
我算了一下,大概要13000个字符...
这样应该够了