游标中select * into 变量不能实现，求解。在线等待

[sckdcs]

declare @dwdm varchar(10),@dwmc varchar(40)
declare hsdwmc cursor for
select dwdm,dwmc from dbo.zt_DW
open hsdwmc
fetch next from hsdwmc into @dwdm,@dwmc
while @@FETCH_STATUS =0
begin
declare @newpzbname1 varchar(44)
set @newpzbname1 =(@dwmc+'行政凭证')
print @newpzbname1 --这里能正常输出
select Jzrq,yf,Pzfh,Kmdm,fj,Zy,Jje,Dje into @newpzbname1 from dbo.zw_PZMXls where dwdm =@dwdm --这里却不能用这个变量作为表名
end
close hsdwmc
deallocate hsdwmc

[Haytor]

select * into 只能写入临时表，不能写入表变量，你的表变量定义也是错误，你那个只能是变量的定义和赋值。

[Haytor]

如果一定要实现上面的功能，你可以参考一下这个动态创建临时表的方案，仅供参考：

DECLARE @dwdm VARCHAR(10),@dwmc VARCHAR(40)
DECLARE @SQL NVARCHAR(MAX)

DECLARE @newpzbname1 varchar(44)

DECLARE hsdwmc CURSOR for
SELECT dwdm,dwmc FROM dbo.zt_DW
OPEN hsdwmc

FETCH NEXT FROM hsdwmc INTO @dwdm,@dwmc
WHILE @@FETCH_STATUS =0
BEGIN

	SET @newpzbname1 = ''
	SET @newpzbname1 = '#' + @dwmc+'行政凭证'
	PRINT @newpzbname1 --这里能正常输出

	SET @SQL = '
	IF OBJECT_ID(''tempdb..'+@newpzbname1+''') IS NOT NULL DROP TABLE '+ @newpzbname1 +'
	SELECT 
		Jzrq,yf,Pzfh,Kmdm,fj,Zy,Jje,Dje
	INTO ' + @newpzbname1 + '
	FROM dbo.zw_PZMXls WHERE 1=2

	INSERT '+@newpzbname1+'
	SELECT 
		Jzrq,yf,Pzfh,Kmdm,fj,Zy,Jje,Dje 
	FROM 
		dbo.zw_PZMXls 
	WHERE 
		dwdm = ' +@dwdm+ '
	
	SELECT * FROM '+ @newpzbname1 +'
	'
	PRINT @SQL
	EXEC sp_executesql @SQL

	FETCH NEXT FROM hsdwmc INTO @dwdm,@dwmc
END

CLOSE hsdwmc
DEALLOCATE hsdwmc

[sckdcs]

周一去测试，通过了马上就给分哈。谢谢。

[sckdcs]

IF OBJECT_ID('tempdb..#积善乡财政总预算00000行政凭证') IS NOT NULL DROP TABLE #积善乡财政总预算00000行政凭证 SELECT Jzrq,yf,Pzfh,Kmdm,fj,Zy,Jje,Dje INTO #积善乡财政总预算00000行政凭证 FROM dbo.zw_PZMXls WHERE dwdm = 88834 执行结果，好象是成功，但找不到表在哪里，已经刷新过了的。是不是临时表呢？ to Haytor 大哥能留个联系方式吗，想通过电话请教。

[gw6328]

基本的语法问题 你能 delcare @a nvarchar(10)='tba' select * from @a ; 这样吗？

[Haytor]

我给你的方案是创建的临时表，只能在动态创建临时表的会话里面可以使用。 提供的方案只是告诉你，这样写是可以实现的。但不是一个好的决解方案。 我不知道你那边查出来的数据为何什么要放到不同表里面，你完全可以根据不同条件返回不同的结果集就可以了。 如果你一定要写到表里面，你就把临时表修改成真正的表就可以了。

[sckdcs]

--1产生余额表 DECLARE @dwdm VARCHAR(10),@dwmc VARCHAR(40) DECLARE @SQL NVARCHAR(MAX) DECLARE @yeb varchar(44) declare str_yeb cursor for select dwdm,dwmc from dbo.zt_DW open str_yeb fetch next from str_yeb into @dwdm,@dwmc while @@FETCH_STATUS =0 begin SET @yeb = '' set @yeb = '##' + @dwmc+'余额表' SET @SQL = 'IF OBJECT_ID(''tempdb..'+@yeb+''') IS NOT NULL DROP TABLE '+ @yeb +' SELECT Kmdm,Kmmc,Yefx,Ncye INTO ' + @yeb + ' FROM dbo.zw_kmini WHERE dwdm = ' +@dwdm+ '' EXEC sp_executesql @SQL fetch next from str_yeb into @dwdm,@dwmc end close str_yeb deallocate str_yeb go --2产生代码表 DECLARE @dwdm VARCHAR(10),@dwmc VARCHAR(40) DECLARE @SQL1 NVARCHAR(MAX) DECLARE @dmb varchar(44) declare str_dmb cursor for select dwdm,dwmc from dbo.zt_DW open str_dmb fetch next from str_dmb into @dwdm,@dwmc while @@FETCH_STATUS =0 begin SET @dmb = '' set @dmb = '##' + @dwmc+'代码表' SET @SQL1 = 'IF OBJECT_ID(''tempdb..'+@dmb+''') IS NOT NULL DROP TABLE '+ @dmb +' SELECT Kmdm,Kmmc,Yefx INTO ' + @dmb + ' FROM dbo.zw_KMB WHERE dwdm = ' +@dwdm+ '' EXEC sp_executesql @SQL1 fetch next from str_dmb into @dwdm,@dwmc end close str_dmb deallocate str_dmb go --3乡镇行政凭证 DECLARE @dwdm VARCHAR(10),@dwmc VARCHAR(40) DECLARE @SQL2 NVARCHAR(MAX) DECLARE @pzb1 varchar(44) declare str_pzb1 cursor for select dwdm,dwmc from dbo.zt_DW open str_pzb1 fetch next from str_pzb1 into @dwdm,@dwmc while @@FETCH_STATUS =0 begin SET @pzb1 = '' SET @pzb1 = '##' + @dwmc+'乡镇行政凭证' SET @SQL2 = 'IF OBJECT_ID(''tempdb..'+@pzb1+''') IS NOT NULL DROP TABLE '+ @pzb1 +' SELECT Jzrq,yf,Pzfh,Kmdm,fj,Zy,Jje,Dje INTO ' + @pzb1 + ' FROM dbo.zw_PZMXls WHERE dwdm = ' +@dwdm+ '' EXEC sp_executesql @SQL2 fetch next from str_pzb1 into @dwdm,@dwmc end close str_pzb1 deallocate str_pzb1 go --4乡镇行政月凭证 DECLARE @dwdm VARCHAR(10),@dwmc VARCHAR(40) DECLARE @SQL3 NVARCHAR(MAX) DECLARE @pzb2 varchar(44) declare str_pzb2 cursor for select dwdm,dwmc from dbo.zt_DW open str_pzb2 fetch next from str_pzb2 into @dwdm,@dwmc while @@FETCH_STATUS =0 begin SET @pzb2 = '' SET @pzb2 = '##' + @dwmc+'乡镇行政凭证' SET @SQL3 = 'IF OBJECT_ID(''tempdb..'+@pzb2+''') IS NOT NULL DROP TABLE '+ @pzb2 +' SELECT Jzrq,month(Jzrq) as yf,Pzfh,Kmdm,fj,Zy,Jje,Dje INTO ' + @pzb2 + ' FROM dbo.zw_PZMX WHERE dwdm = ' +@dwdm+ '' EXEC sp_executesql @SQL3 fetch next from str_pzb2 into @dwdm,@dwmc end close str_pzb2 deallocate str_pzb2

[sckdcs]

Haytor 确实是高手。这个论坛没有办法给私信，要是有法给私信就好了。

[sckdcs]

这个是其他软件要用到三个表（凭证表，余额表，代码表，因工作需要，不涉及到任何经济），本想通过一个循环一次实现，测试了一下，产行，因此写了4个循环（没完还要把凭证表合并了)

[中国风]

declare @dwdm varchar(10),@dwmc varchar(40),@Sql NVARCHAR(max)
declare hsdwmc cursor for
select dwdm,dwmc from dbo.zt_DW
open hsdwmc
fetch next from hsdwmc into @dwdm,@dwmc
while @@FETCH_STATUS =0
begin
declare	@newpzbname1 varchar(44)
        set @newpzbname1 =(@dwmc+'行政凭证')
print @newpzbname1 --这里能正常输出
--动态传参
SET @Sql='SELECT Jzrq,yf,Pzfh,Kmdm,fj,Zy,Jje,Dje into @newpzbname1 from '+@dwmc+' where dwdm =@dwdm' --这里却不能用这个变量作为表名
EXEC sys.sp_executesql @Sql
					,N'@dwdm varchar(10)'
					,@dwdm
fetch next from hsdwmc into @dwdm,@dwmc		--加上循环游标			
end
close hsdwmc
deallocate hsdwmc