CREATE TABLE TB([field1] varchar(100), [field2] varchar(100), [field3] varchar(100))
INSERT INTO TB
SELECT 'aaa', 'bbb', 'ccc'
UNION ALL SELECT 'ddd', 'eee', 'fff'
DECLARE @SQL VARCHAR(8000)
SET @SQL = 'SELECT ''INSERT INTO TMP('
SELECT @SQL = @SQL + COLUMN_NAME + ',' FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'TB' ORDER BY ORDINAL_POSITION
SET @SQL = LEFT(@SQL, LEN(@SQL) - 1) + ') VALUES(''''''+'
SELECT @SQL = @SQL + COLUMN_NAME + '+'''''',''''''+' FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'TB' ORDER BY ORDINAL_POSITION
SET @SQL = LEFT(@SQL, LEN(@SQL) - 5)
EXEC(@SQL + ')'' FROM TB')
DROP TABLE TB
结果
INSERT INTO TMP(field1,field2,field3) VALUES('aaa','bbb','ccc')
INSERT INTO TMP(field1,field2,field3) VALUES('ddd','eee','fff')