如何在sql server中使用2个临时表 [英] how to use 2 temp table in sql server

问题描述

大家好..



i想在sql server 2008 r2中使用2个临时表..我得到错误..请给我corerct语法..



错误是：插入语句附近的语法不正确...错误在第二个插入语句中.. ex：

 插入 进入＃tblagereoprt1 

我的存储过程是：

 USE [scjdata] 
 GO 
 / ******对象：StoredProcedure [ dbo]。[alldistt_wise]脚本日期：05/15/2014 10:58:59 ****** / 
 SET ANSI_NULLS ON 
 GO 
 SET QUOTED_IDENTIFIER ON 
 GO 
 
 ALTER PROCEDURE [dbo]。[alldistt_wise] 
 
 @Sex VARCHAR（250），
 @DisabiltyType VARCHAR（250），
 @SocialGroup VARCHAR （250），
 @Minority VARCHAR（250），
 @Age_start VARCHAR（250），
 @Age_End VARCHAR（250）
 AS 
 BEGIN 
设置NOCOUNT ON; 
 DECLARE 
 @ChildTblName AS VARCHAR（125），
 @FamilyTblName AS VARCHAR（125），
 @vQuery AS VARCHAR（max），
 @ vQuery1 as varchar（最大）; 
 
 
 
 
 CREATE TABLE #tblagereoprt 
（
 disttname nvarchar（20）
 
）; 
 
 CREATE TABLE＃tblagereoprt1 
（
 
 totalpopulation nvarchar（20）
）; 
 
 
 DECLARE @N varchar（3）
 SET @N ='1'
 WHILE（@N< = 27）
 BEGIN 
 SET @ChildTblName ='['+ RIGHT（（'0'+ @ N），2）+'Child_D'+'''
 SET @FamilyTblName ='['+ RIGHT（（'0'+ @N），2）+'Family_D'+'''
 
 
 
 SET @vQuery ='从DIST中选择distname作为disttname，其中distt ='+ @ N +'' ; 
 
 
 
 SET @ vQuery1 ='select count（Child.cname）作为总人口从'+ @ChildTblName +'作为Child INNER JOIN'+ @FamilyTblName +'
 AS Family ON Child.barcode = Family.barcode其中Child.distt ='+ @ N +''
 
 IF（@Sex<>''）
 BEGIN 
 SET @vQuery = @vQuery +'AND Child.sex ='''+ @Sex +''''
 END 
 IF（@DisabiltyType<>''）
 BEGIN 
 SET @vQuery = @vQuery +'AND Child.disability ='''+ @DisabiltyType +''''
 END 
 IF（@Age_start<>''和@Age_End< ;>''）
 BEGIN 
 SET @vQuery = @vQuery +'AND（Child.age介于'''+ @Age_start +'''和'''+ @Age_End +'''' ）'
 END 
 
 IF（@SocialGroup<>''）
 BEGIN 
 SET @vQuery = @vQuery +'AND Family.social_grp ='' '+ @Social Group +''''
 END 
 IF（@Minority<>''）
 BEGIN 
 SET @vQuery = @vQuery +'AND Family.minority ='' '+ @Minority +''''
 END; 
 
插入#tblagereoprt 
插入＃tblagereoprt1 
 
 exec（@ vQuery1）
 exec（@vQuery）
 
 SET @N = @N +1 
 END 
 select * from #tblagereoprt 
 select * from＃tblagereoprt1 
 drop table #tblagereoprt; 
 drop table＃tblagereoprt1; 
 
 
 END 

解决方案

尝试更改这些行

 插入 进入 #tblagereoprt 
 插入 进入＃tblagereoprt1 
 
  exec （ @ vQuery1 ）
  exec （ @ vQuery ）

to

 插入 进入 #tblagereoprt 
  Exec （ @ vQuery ）
 
  insert   into ＃tblagereoprt1 
  exec （ @ vQuery1 ）
 

hi all..

i want to use 2 temp table in sql server 2008 r2.. i m getting error.. please give me corerct syntax..

the error is: incorrect syntax near insert statement..the error is in second insert statement.. ex:

insert into #tblagereoprt1

my stored procedure is:

  USE [scjdata]
GO
/****** Object:  StoredProcedure [dbo].[alldistt_wise]    Script Date: 05/15/2014 10:58:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[alldistt_wise] 
	
    @Sex VARCHAR (250),
    @DisabiltyType VARCHAR (250),
    @SocialGroup  VARCHAR (250),
    @Minority VARCHAR (250),
    @Age_start VARCHAR (250),
    @Age_End VARCHAR (250)
AS
BEGIN	
	SET NOCOUNT ON;
	DECLARE
	@ChildTblName AS VARCHAR (125), 
	@FamilyTblName AS VARCHAR (125), 
    @vQuery AS VARCHAR (max), 
    @vQuery1 as varchar(max);
    
    
   
    
      CREATE TABLE #tblagereoprt
    ( 
       disttname nvarchar(20)
     
    );
    
        CREATE TABLE #tblagereoprt1
    ( 
    
       totalpopulation   nvarchar(20)  
    );
    
    
    DECLARE @N varchar(3)
    SET @N = '1' 
    WHILE (@N <= 27)
    BEGIN
    SET @ChildTblName = '[' + RIGHT(('0'+@N),2) + 'Child_D' + ']'
    SET @FamilyTblName = '[' + RIGHT(('0'+@N),2) + 'Family_D' + ']'
    
     
      
    SET @vQuery = 'select distname as disttname from DIST where distt='+@N+'';
    
   
    
    SET @vQuery1 = 'select count(Child.cname) as totalpopulation from ' + @ChildTblName + ' As Child  INNER JOIN ' + @FamilyTblName + ' 
      AS Family  ON Child.barcode=Family.barcode where Child.distt='+@N+'' 
     
    IF(@Sex <> '')
    BEGIN
        SET @vQuery = @vQuery + ' AND Child.sex = ''' + @Sex + ''''
    END  
    IF(@DisabiltyType <> '')
    BEGIN
        SET @vQuery = @vQuery + ' AND Child.disability = ''' + @DisabiltyType + ''''
    END 
    IF(@Age_start <> '' and @Age_End <> '')
    BEGIN
        SET @vQuery = @vQuery + ' AND (Child.age between ''' + @Age_start + ''' and ''' + @Age_End + ''')'
    END 
    
    IF(@SocialGroup <> '')
    BEGIN
        SET @vQuery = @vQuery + ' AND Family.social_grp = ''' + @SocialGroup + ''''
    END 
    IF(@Minority <> '')
    BEGIN
        SET @vQuery = @vQuery + ' AND Family.minority = ''' + @Minority + ''''
    END; 
    
    insert into #tblagereoprt
    insert into #tblagereoprt1
    
   exec(@vQuery1)
    exec(@vQuery)
   
    SET @N = @N +1
    END
    select * from  #tblagereoprt 
     select * from  #tblagereoprt1 
    drop table #tblagereoprt;
    drop table #tblagereoprt1;
  
  
END

解决方案

Try changing these lines

insert into #tblagereoprt
    insert into #tblagereoprt1

   exec(@vQuery1)
    exec(@vQuery)

to

insert into #tblagereoprt
Exec(@vQuery)

insert into #tblagereoprt1
exec(@vQuery1)

这篇关于如何在sql server中使用2个临时表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！