SQL获取其他表里的字段名并作为数据存到总表
1.根据表名,先获取指定表里的字段,查询结果存储为临时表(加ID列)
declare @objid int,@objname char(40)
set @objname = 'TableName'
select @objid = id from sysobjects where id = object_id(@objname)
SELECT RANK() OVER (order by colid) AS 'ID','Column_name' = name INTO #TableName from syscolumns where id = @objid order by colid
2.查询刚刚生成临时表的数据条数,记下后面要用
SELECT COUNT(*) FROM #TableName2
3.用while循环将临时表信息插入总表(insert语句根据具体情况而定)
declare @columnName nvarchar(200),@rows int,@total int
set @rows=1
while @rows<刚刚查出的总条数+1
begin
SELECT @columnName= Column_name FROM #TableName WHERE ID=@rows
--根据ID查出临时表里的数据,放到变量里
--print @columnName
INSERT INTO 总表名字
--(总表的字段)VALUES(对应的数据)
(ATTRNAME, ATTRVALUE,Valid,ATTRNAME1,ATTRVALUE1)
VALUES ('ATTRNAME', 'Data',1,'ColumnName',@columnName)
set @rows=@rows+1
end