比如,select table_name from PM_TABLE,查询的结果是一系列的表名,然后我要分别往每个表内插入数据,请问,这样的存储过程该怎么写?这是我写的,可通不过IF EXISTS (SELECT name from sysobjects where name='PROC_AGGREGATE_DATA' AND type='P')
DROP PROCEDURE PROC_AGGREGATE_DATA
GO
CREATE PROCEDURE PROC_AGGREGATE_DATA
@minTblName VARCHAR(50), @maxTblName VARCHAR(50)
AS
IF (select count(TABLE_NAME) from pm_cd_table
where TABLE_NAME>=@minTblName and TABLE_NAME<=@maxTblName
)>0
BEGIN
declare my_cursor cursor
for
select TABLE_NAME from pm_cd_table where TABLE_NAME>=@minTblName and TABLE_NAME<=@maxTblName open my_cursor
declare @name varchar(40)
fetch next from my_cursor into @name
while(@@fetch_status=0)
begin
print '表名: ' + @name
TRUNCATE TABLE PM_COLLECTED_DATA_TEMP
INSERT INTO PM_COLLECTED_DATA_TEMP(DIM_TIME_ID,VALUE,P_ID,MONAME)
SELECT DIM_TIME_ID,VALUE,P_ID,MONAME
FROM @name
WHERE DIM_TIME_ID=2006061312 --select datepart(year,getdate())*1000000+datepart(month,getdate())*10000+datepart(day,getdate())*100+datepart(hour,getdate())
fetch next from my_cursor into @name
end close my_cursor
deallocate my_cursor
ENDELSE
PRINT '查询的TABLE_NAME为空'问题在于 FROM @name 这句,好象@name是表名称的字符串,而不是表名,因此报错。
DROP PROCEDURE PROC_AGGREGATE_DATA
GO
CREATE PROCEDURE PROC_AGGREGATE_DATA
@minTblName VARCHAR(50), @maxTblName VARCHAR(50)
AS
IF (select count(TABLE_NAME) from pm_cd_table
where TABLE_NAME>=@minTblName and TABLE_NAME<=@maxTblName
)>0
BEGIN
declare my_cursor cursor
for
select TABLE_NAME from pm_cd_table where TABLE_NAME>=@minTblName and TABLE_NAME<=@maxTblName open my_cursor
declare @name varchar(40)
fetch next from my_cursor into @name
while(@@fetch_status=0)
begin
print '表名: ' + @name
TRUNCATE TABLE PM_COLLECTED_DATA_TEMP
INSERT INTO PM_COLLECTED_DATA_TEMP(DIM_TIME_ID,VALUE,P_ID,MONAME)
SELECT DIM_TIME_ID,VALUE,P_ID,MONAME
FROM @name
WHERE DIM_TIME_ID=2006061312 --select datepart(year,getdate())*1000000+datepart(month,getdate())*10000+datepart(day,getdate())*100+datepart(hour,getdate())
fetch next from my_cursor into @name
end close my_cursor
deallocate my_cursor
ENDELSE
PRINT '查询的TABLE_NAME为空'问题在于 FROM @name 这句,好象@name是表名称的字符串,而不是表名,因此报错。
先定义:declare @Sql varchar(8000)open my_cursor
declare @name varchar(40)
fetch next from my_cursor into @name
while(@@fetch_status=0)
begin
print '表名: ' + @nameset @Sql='TRUNCATE TABLE PM_COLLECTED_DATA_TEMP
INSERT INTO PM_COLLECTED_DATA_TEMP(DIM_TIME_ID,VALUE,P_ID,MONAME)
SELECT DIM_TIME_ID,VALUE,P_ID,MONAME
FROM '+@name+'
WHERE DIM_TIME_ID=2006061312' --select datepart(year,getdate())*1000000+datepart(month,getdate())*10000+datepart(day,getdate())*100+datepart(hour,getdate())exec(@Sql)
fetch next from my_cursor into @name
end